Forum Discussion
Append Query of 3 Tables Returns Records from One Table 3 Times and None from Other 2
Hi,
First time poster and new to Power BI, I have come across a curious issue.
We have recently implemented Business Central 365 in a private cloud, running 3 companies. I am trying to extract a summary of some General Ledger tranasactions and balances across all three entities. I have connected to 2 OData tables (GL Card and GL Entries) for each company, successfully merged them and done the necessary calculations, filtering and column removals I wanted and have successfully displayed the results for all 3 companies - CHO has 380 records, CHM has 209 records and CHN has 15 records.
Now when I attempt to combine them all into one using "Append Queries" I get 1140 records with the result being the 380 records for the company CHO repeated 3 times with no records for CHM or CHN.
I know the columns in my original 3 source queries are identical because I created one query first and then copied it and changed the source for the other two.
This is doing my head in and I would appreciate any possible assistence and completing what I thought should be a fairly straight forward task.
Also, I forgot to mention, I also tried creating a table under Modeling using the UNION command and still ended up with nonsense data.
2 Replies
AnkitBISolution Sage
Can you share steps from Advanced QueriesThe following is the query to extract the records required from the "Header Table":
let Source = Dynamics365BusinessCentralOnPremises.Contents("https://HOST URL/OData", null), #"COMPANY FILE NAME" = Source{[Name="COMPANY FILE NAME"]}[Data], G_L_Account_Card_table = #"COMPANY FILE NAME"{[Name="G_L_Account_Card",Signature="table"]}[Data], #"Filtered Rows" = Table.SelectRows(G_L_Account_Card_table, each ([Account_Type] = "Posting")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([No], "110") or Text.StartsWith([No], "111") or Text.StartsWith([No], "112") or Text.StartsWith([No], "113") or Text.StartsWith([No], "25")) in #"Filtered Rows1"And then I extract, merge and manipulate records from the Transaction Detail Table:
let Source = Dynamics365BusinessCentralOnPremises.Contents("https:// HOST URL/OData", null), #"COMPANY FILE NAME" = Source{[Name="COMPANY FILE NAME"]}[Data], G_LEntries_table = #"COMPANY FILE NAME"{[Name="G_LEntries",Signature="table"]}[Data], #"Merged Queries1" = Table.NestedJoin(G_LEntries_table, {"G_L_Account_No"}, #"Bank Account Extract CHO", {"No"}, "Bank Account Extract CHO", JoinKind.RightOuter), #"Expanded Bank Account Extract CHO" = Table.ExpandTableColumn(#"Merged Queries1", "Bank Account Extract CHO", {"Name", "Balance"}, {"Bank Account Extract CHO.Name", "Bank Account Extract CHO.Balance"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Bank Account Extract CHO", each [Posting_Date] <> null and [Posting_Date] <> ""), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Entry_No", "Transaction_No", "G_L_Account_No", "Document_Date", "Document_Type", "Document_No", "Source_Code", "Job_No", "Business_Unit_Code", "Reason_Code", "Gen_Posting_Type", "Gen_Bus_Posting_Group", "Gen_Prod_Posting_Group", "Tax_Area_Code", "Tax_Liable", "Tax_Group_Code", "Use_Tax", "VAT_Bus_Posting_Group", "VAT_Prod_Posting_Group", "IC_Partner_Code", "Debit_Amount", "Credit_Amount", "VAT_Amount", "Additional_Currency_Amount", "Add_Currency_Debit_Amount", "Add_Currency_Credit_Amount", "Dimension_Set_ID", "G_L_Account_Name", "AuxiliaryIndex1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Bank Account Extract CHO.Name", "Account Name"}, {"Bank Account Extract CHO.Balance", "Curr Bal"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "YrEnd", each #date(if Date.Month (DateTime.FixedLocalNow()) > 6 then Date.Year(DateTime.FixedLocalNow()) else Date.Year(DateTime.FixedLocalNow())-1, 6, 30)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "WeekFrom", each Date.AddDays(DateTime.LocalNow(),-8)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "WeekTo", each Date.AddDays(DateTime.LocalNow(),-1)), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MonthDate", each Date.StartOfMonth (DateTime.LocalNow())), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"YrEnd", type date}, {"WeekFrom", type date}, {"WeekTo", type date}, {"MonthDate", type date}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Posting_Date] > [YrEnd]), #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "MonthVal", each if [Posting_Date] >= [MonthDate] then [Amount] else 0), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "WeekVal", each if [Posting_Date] <= [WeekFrom] then 0 else if [Posting_Date] > [WeekTo] then 0 else [Amount]), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "TodayVal", each if [Posting_Date] > [WeekTo] then [Amount] else 0), #"Added Custom4" = Table.AddColumn(#"Added Conditional Column2", "Cl Bal", each [Curr Bal] - [TodayVal]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"MonthVal", type number}, {"WeekVal", type number}, {"Cl Bal", type number}, {"TodayVal", type number}}), #"Added Custom5" = Table.AddColumn(#"Changed Type1", "Unit", each "CHO") in #"Added Custom5"As mentioned in my original post there are three sets of queries as above (which is just for the CHO company) to extract the data from each company file. I then attempted to merge all three using the following query.
let Source = #"Bank Transaction Extract CHO", #"Appended Query" = Table.Combine({Source, #"Bank Transaction Extract CHN", #"Bank Transaction Extract CHM"}) in #"Appended Query"As I mentioned in my original post, I am new to Power BI and Power Query, so I may be going about this all wrong, but I would have thought that given each of the Bank Transaction Extract queries previews the correct data, then the combining of all three should work – but it doesn’t.
Any help that can be provided would be much appreciated.
Cheers,
Howard.