Business Central with multiple companies duplicates data

[jefffenn]

Hi,

I'm using Power BI and the dynamics BC connector to pull data from multiple Dynamics Business Central companies.  I grabbing the GL Entries table data for each company (6 in total).  I then append all of the data into one consolidated table.  For some strange reason, the count the transactions in each company is exactly the same.  The data seems to be repeating from one of the companies across all companies. 

I've also tried the other technique of using 1 query across all companies outlined in the link below, but have the same result. Data is duplicating from 1 company across all companies. 

https://thinkaboutit.be/2019/06/how-do-i-create-a-multi-company-power-bi-report-with-the-business-ce...

Anyone have any ideas besides going the route of having to use the OData feeds? Would prefer to use the connector.

Thanks,

-Jeff

[Anonymous]

Hi @jefffenn  - 

In the Power Query Editor, go to the Advanced Editor for each source table and check the source. It seems the data source may have been overwritten for each connection to Dynamics. I'm not sure whether it would work to provide the connection info within each Advanced Editor. Please let us know whether that works.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[jefffenn]

HI,

I'm hitting Business Central so the source is the same for each company query.  I have two examples below.  (1) The first is the Advanced Editor Code making 3 separate calls to 3 different BC companies to pull General_Ledger_Entries table.  I would then combine them all with an append.  (2) The second example is using 1 query to pull data for all of the companies for the General_Ledger_Entries table.  Both examples have the same problem and duplicate data.

(1) 3 queries for 3 companies: Westwood, Beverly Hills, and Manhattan Beach.  Data is repeated for Beverly Hills.

let
Source = Dynamics365BusinessCentral.EnvironmentContents(null, null),
Production = Source{[Name="Production"]}[Data],
#"Beverly Hills" = Production{[Name="Beverly Hills"]}[Data],
General_Ledger_Entries_table = #"Beverly Hills"{[Name="General_Ledger_Entries",Signature="table"]}[Data]
in
General_Ledger_Entries_table

let
Source = Dynamics365BusinessCentral.EnvironmentContents(null, null),
Production = Source{[Name="Production"]}[Data],
#"Manhattan Beach" = Production{[Name="Manhattan Beach"]}[Data],
General_Ledger_Entries_table = #"Manhattan Beach"{[Name="General_Ledger_Entries",Signature="table"]}[Data]
in
General_Ledger_Entries_table

let
Source = Dynamics365BusinessCentral.EnvironmentContents(null, null),
Production = Source{[Name="Production"]}[Data],
Westwood = Production{[Name="Westwood"]}[Data],
General_Ledger_Entries_table = Westwood{[Name="General_Ledger_Entries",Signature="table"]}[Data]
in
General_Ledger_Entries_table

(2) 1 query to return data for all companies.  Has the same problem with repeating data.

let
Source = Dynamics365BusinessCentral.Contents(null),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Display_Name", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Name", "Data", "Signature"}, {"Data.Name", "Data.Data", "Data.Signature"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Data.Signature"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Data.Name] = "General_Ledger_Entries")),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data.Data", {"Entry_No", "Posting_Date", "Document_Type", "Document_No", "G_L_Account_No", "G_L_Account_Name", "Description", "Job_No", "Global_Dimension_1_Code", "Global_Dimension_2_Code", "IC_Partner_Code", "Gen_Posting_Type", "Gen_Bus_Posting_Group", "Gen_Prod_Posting_Group", "Quantity", "Amount", "Debit_Amount", "Credit_Amount", "Additional_Currency_Amount", "VAT_Amount", "Bal_Account_Type", "Bal_Account_No", "User_ID", "Source_Code", "Reason_Code", "Reversed", "Reversed_by_Entry_No", "Reversed_Entry_No", "FA_Entry_Type", "FA_Entry_No", "Dimension_Set_ID", "External_Document_No", "Job_No_Link", "Gen_Bus_Posting_Group_Link", "Gen_Prod_Posting_Group_Link", "Reversed_by_Entry_No_Link", "Reversed_Entry_No_Link", "FA_Entry_No_Link", "ETag"}, {"Data.Data.Entry_No", "Data.Data.Posting_Date", "Data.Data.Document_Type", "Data.Data.Document_No", "Data.Data.G_L_Account_No", "Data.Data.G_L_Account_Name", "Data.Data.Description", "Data.Data.Job_No", "Data.Data.Global_Dimension_1_Code", "Data.Data.Global_Dimension_2_Code", "Data.Data.IC_Partner_Code", "Data.Data.Gen_Posting_Type", "Data.Data.Gen_Bus_Posting_Group", "Data.Data.Gen_Prod_Posting_Group", "Data.Data.Quantity", "Data.Data.Amount", "Data.Data.Debit_Amount", "Data.Data.Credit_Amount", "Data.Data.Additional_Currency_Amount", "Data.Data.VAT_Amount", "Data.Data.Bal_Account_Type", "Data.Data.Bal_Account_No", "Data.Data.User_ID", "Data.Data.Source_Code", "Data.Data.Reason_Code", "Data.Data.Reversed", "Data.Data.Reversed_by_Entry_No", "Data.Data.Reversed_Entry_No", "Data.Data.FA_Entry_Type", "Data.Data.FA_Entry_No", "Data.Data.Dimension_Set_ID", "Data.Data.External_Document_No", "Data.Data.Job_No_Link", "Data.Data.Gen_Bus_Posting_Group_Link", "Data.Data.Gen_Prod_Posting_Group_Link", "Data.Data.Reversed_by_Entry_No_Link", "Data.Data.Reversed_Entry_No_Link", "Data.Data.FA_Entry_No_Link", "Data.Data.ETag"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data.Data",{{"Name", "Company Name"}, {"Data.Name", "Name"}, {"Display_Name", "Display Name"}, {"Data.Data.Entry_No", "Entry No"}, {"Data.Data.Posting_Date", "Posting Date"}, {"Data.Data.Document_Type", "Document Type"}})
in
#"Renamed Columns"

[MSAInitaite]

Hi

After some testing we found that the connections are getting reused for some reason. So even thoug I would like to get G_L_Entry from diferent companies we got the first one.  We realised that if we used then same environment name we god duplicates, but if we used different casing we got the correct data (so it looks so far). We used the below code to get the correct data. Looks like if we use different casing in all the connections they are treated as seperate connections. If we use the same case (e.g. PRODUCTION then it is treated as diffent queries. 

Hope this helps - it seems to have solved our issue.  

let
Source = Dynamics365BusinessCentral.EnvironmentContents("Production", "CompanyOne"),
G_LEntries_table = Source{[Name="G_LEntries",Signature="table"]}[Data]

let
Source = Dynamics365BusinessCentral.EnvironmentContents("pRoduction", "CompanyTwo"),
G_LEntries_table = Source{[Name="G_LEntries",Signature="table"]}[Data]

[pablopsoto]

Hello,

I had the same issue and your solution solved the problem. It's a bit messy because I have many tables but I hope Power BI solves this problem soon.

Thank you, you saved me

[Anonymous]

I am facing the exact same problem. Also connecting via Business Central and pulling from GLEntries, as well as DimSetEntries.
In both cases this problem occurs where the data from one company seems to be duplicated.

Hope that this will be adressed asap.