Het probleem treedt zowel op vanuit Power BI Desktop als vanuit powerbi.com.
Vanuit Power BI Desktop is de query (request ID 0HN3AI3I6B6NR:00000001
):
select t.[Account], t.[AmountDC], t.[CostCenter], t.[CostUnit], t.[Date], t.[Description], t.[Division], t.[EntryID], t.[FinancialPeriod], t.[FinancialYear], t.[GLAccount], t.[InvoiceNumber], t.[JournalCode]
from ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
where ([AmountDC] is not null )
Laatste meldingscode bij voortgang is:
itgenboe154
Start yielding data.
Er zijn geen andere request actief onder hetzelfde abonnement.
Na 121 seconden treedt op:
itgenboe237
Log failed retrieval. So far retrieved 55,363 rows on ‘ExactOnlineREST.Incremental.TransactionLinesIncremental’ in 118,859 ms.
en dan:
itgenlee011
itgenpmr038: The execution was cancelled.
De Power Query-code voor de dataset (“Fact”) is:
let
Source = Table.Combine({#"Temp_On-balance sheet entries",#"Temp_On-balance sheet entries_2", #"Temp_On-balance sheet entries_3" ,#"Temp_Off-balance sheet entries", Temp_Budget})
in
Source
De Power Query-code voor de dataset (“Temp_On-balance sheet entries”) is:
let
Source = OData.Feed("https://bridge-online.cloud/acme-exact-online/odata4", null, [Implementation="2.0", Timeout=#duration(0,4,0,0)]),
#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
#"__Removed Other Columns" = Table.SelectColumns(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table",{"Account", "AmountDC", "CostCenter", "CostUnit", "Date", "Description", "Division", "EntryID", "FinancialPeriod", "FinancialYear", "GLAccount", "InvoiceNumber", "JournalCode"}),
Custom1 = #"__Removed Other Columns",
#"Filtered Rows" = Table.SelectRows(Custom1, each ([AmountDC] <> null)),
#"Merged Temp_Document" = Table.NestedJoin(#"Filtered Rows", {"EntryID", "Division"}, Temp_Document, {"FinancialTransactionEntryID", "Division"}, "Temp_Document", JoinKind.LeftOuter),
#"__Expanded [DocumentURL]" = Table.ExpandTableColumn(#"Merged Temp_Document", "Temp_Document", {"DocumentViewUrl"}, {"DocumentViewUrl"}),
#"Added [Invoice date]" = Table.AddColumn(#"__Expanded [DocumentURL]", "Invoice date", each [Date]),
#"__Changed Type" = Table.TransformColumnTypes(#"Added [Invoice date]",{{"Date", type date}, {"Invoice date", type date}}),
#"__Renamed Columns" = Table.RenameColumns(#"__Changed Type",{{"Date", "TEMP_Date"}, {"Account", "Relation ID"}, {"AmountDC", "Amount"}, {"Division", "%Entitykey"}, {"GLAccount", "%Local account number key"}, {"CostCenter", "Cost center code"}, {"CostUnit", "Cost unit code"}, {"FinancialPeriod", "Financial period"}, {"FinancialYear", "Financial year"}, {"InvoiceNumber", "Invoice number"}, {"JournalCode", "JournalCode"}}),
#"__Added [Datatype]" = Table.AddColumn(#"__Renamed Columns", "Datatype", each "Actuals", Text.Type),
#"__Added [Datasubtype]" = Table.AddColumn(#"__Added [Datatype]", "Datasubtype", each "Boekhouding", Text.Type),
#"Merged Temp_Entity" = Table.NestedJoin(#"__Added [Datasubtype]", {"%Entitykey"}, Temp_Entity, {"%Entitykey"}, "Temp_Entity", JoinKind.LeftOuter),
#"__Expanded [Entity]" = Table.ExpandTableColumn(#"Merged Temp_Entity", "Temp_Entity", {"Description", "Entity group"}, {"Entity", "Entity group"}),
#"__Filtered [Entity] <> null" = Table.SelectRows(#"__Expanded [Entity]", each ([Entity] <> null)),
#"Merged Temp_Local accounts" = Table.NestedJoin(#"__Filtered [Entity] <> null", {"%Local account number key"}, #"Temp_Local accounts", {"%Local account number key"}, "Temp_Local accounts", JoinKind.LeftOuter),
#"__Expanded [Local account number]" = Table.ExpandTableColumn(#"Merged Temp_Local accounts", "Temp_Local accounts", {"Local account number"}, {"Local account number"}),
#"Merged Temp_Group accounts" = Table.NestedJoin(#"__Expanded [Local account number]", {"Entity", "Local account number"}, #"Temp_Chart of account mapping", {"Entity", "Local account number"}, "Temp_Group accounts", JoinKind.LeftOuter),
#"__Expanded [Group account number]" = Table.ExpandTableColumn(#"Merged Temp_Group accounts", "Temp_Group accounts", {"Group account number"}, {"Group account number"}),
#"__Added [%Cost unit key]" = Table.AddColumn(#"__Expanded [Group account number]", "%Cost unit key", each [Entity]&"|"&[Cost unit code], Text.Type),
#"Merged Temp_relation" = Table.NestedJoin(#"__Added [%Cost unit key]", {"Relation ID"}, Temp_Relation, {"Relation ID"}, "Temp_Relation", JoinKind.LeftOuter),
#"__Expanded [Relation code]" = Table.ExpandTableColumn(#"Merged Temp_relation", "Temp_Relation", {"Relation code"}, {"Relation code"}),
#"__Added [%Relationkey]" = Table.AddColumn(#"__Expanded [Relation code]", "%Relationkey", each [Entity]& "|" & [Relation code], Text.Type),
#"__Added [%AccountKey]" = Table.AddColumn(#"__Added [%Relationkey]", "%AccountKey", each [Entity]&"|"&[Local account number], Text.Type),
#"__Added [%AccountRelationKey]" = Table.AddColumn(#"__Added [%AccountKey]", "%AccountRelationKey", each [Entity]&"|"&[Relation code]&"|"&[Local account number],Text.Type),
#"Merged Temp_ICO (account)" = Table.NestedJoin(#"__Added [%AccountRelationKey]", {"%AccountKey"}, #"Temp_ICO (account)", {"AccountKey"}, "Temp_ICO (account)", JoinKind.LeftOuter),
#"__Expanded Temp_ICO (account)" = Table.ExpandTableColumn(#"Merged Temp_ICO (account)", "Temp_ICO (account)", {"ICO type"}, {"ICO type1"}),
#"Merged Temp_ICO (relation)" = Table.NestedJoin(#"__Expanded Temp_ICO (account)", {"%Relationkey"}, #"Temp_ICO (relation)", {"RelationKey"}, "Temp_ICO (relation)", JoinKind.LeftOuter),
#"__Expanded Temp_ICO (relation)" = Table.ExpandTableColumn(#"Merged Temp_ICO (relation)", "Temp_ICO (relation)", {"ICO type"}, {"ICO type2"}),
#"Merged Temp_ICO (relation+account)" = Table.NestedJoin(#"__Expanded Temp_ICO (relation)", {"%AccountRelationKey"}, #"Temp_ICO (relation+account)", {"AccountRelationKey"}, "Temp_ICO (relation+account)", JoinKind.LeftOuter),
#"__Expanded Temp_ICO (relation+account)" = Table.ExpandTableColumn(#"Merged Temp_ICO (relation+account)", "Temp_ICO (relation+account)", {"ICO type"}, {"ICO type3"}),
#"Added [ICO]" = Table.AddColumn(#"__Expanded Temp_ICO (relation+account)", "ICO", each if [ICO type1]<>null or [ICO type2]<>null or [ICO type3]<>null then "Yes" else "No", Text.Type),
#"__Removed [ICO Type]" = Table.RemoveColumns(#"Added [ICO]",{"ICO type1", "ICO type2", "ICO type3"}),
#"Merged Temp_Financial periods" = Table.NestedJoin(#"__Removed [ICO Type]", {"Entity", "Financial year", "Financial period"}, #"Temp_Financial periods", {"Entity", "Financial year", "Financial period"}, "Temp_Financial periods", JoinKind.LeftOuter),
#"__Expanded Temp_Financial periods" = Table.ExpandTableColumn(#"Merged Temp_Financial periods", "Temp_Financial periods", {"Start date"}, {"Date"}),
#"__Removed Columns" = Table.RemoveColumns(#"__Expanded Temp_Financial periods",{"TEMP_Date"}),
#"Merged Queries Temp_Journal" = Table.NestedJoin(#"__Removed Columns", {"%Entitykey", "JournalCode"}, Temp_Journal, {"Entity", "Code"}, "Temp_Journal", JoinKind.LeftOuter),
#"__Expanded [Journal]" = Table.ExpandTableColumn(#"Merged Queries Temp_Journal", "Temp_Journal", {"Description"}, {"Journal"}),
#"Added Custom" = Table.AddColumn(#"__Expanded [Journal]", "Currency", each "EUR", type text),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Invoice number", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Amount_EUR", each [Amount], type number)
in
#"Added Custom1"
Het downloaden van de navigatiestap naar TransactionLinesIncremental
leidt tot request ID 0HN3AI3I6B6VK:00000002
. Deze blijft hangen, en wacht op een lockvrijgave door request ID 0HN3AI3I6B6NR:00000001
.
Een nieuwe dataset “Temp_On-balance sheet entries (2)” is gemaakt met enkel TransactionLinesIncremental
en de Bridge Online-dienst is herstart.
De preview met 1000 rijen duurde 89 seconden (request ID 0HN3AMDBF6QK9:00000002
) uit een totaal van 976,962 rijen.