Table.Combine wrong records for two tables with parameters

[Lucian]

Hello,

I need to combine two tables... and this is the basic part.

Because both tables will have same structure and will came from the same OData connection I need to do some parametrization, because it may extend to 3-4 tables.

So I have created a "connection string" query named ServiceUrl:

let
    Source = "https://RealURL:Port/SomeString/ODataV4/"
in
    Source

Now, for each of the table I have a query that will use the above source to get data from a specific table (NAV - SalesData) and adding a new column with the company/table tag:

let
    Source = Dynamics365BusinessCentralOnPremises.Contents(ServiceUrl, null),
    CompanyData = Source{[Name="Comp1Table"]}[Data],
    SalesDashboard_table = CompanyData{[Name="SalesDashboard",Signature="table"]}[Data],
    #"Extracted Date" = Table.TransformColumns(SalesDashboard_table,{{"Posting_Date", DateTime.Date, type date}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Date", "BU_TAG", each "COMP1")
in
    #"Added Custom"

The second query is based on the same structure, except the table name will be "Comp2Table" and the corresponding tag "COMP2"

Now I need both of them combined into a single "consolidated" table and I used this query:

let
    Source = Table.Combine({COMP1_SD, COMP2_SD})
in
    Source

All is fine until I did some small check counting records.

The individual queries return 45k for COMP1 and 25k for COMP2.

The combined query return 90k records and counting based on the added tag will just double the records from the first table.

What is the trick to use queries with parameters so combining them will return proper results?

Kind Regards,

Lucian

[Lucian]

Hello again and a quick update:

I thought the problem may arise because of the "parametrization" so I did the connections "manually". I've used a new file just with that 2 tables and for each one I recreate the connection from scratch. Again, they share the same OData connection for a Navision database with multiple companies and I have to pull data from 2 identical tables but different companies.

I have added for that two tables a new column "BUT_TAG" just to know from which company I pull the data.

Then  used an append query to combine data from that two tables and the problem persist - different number of records than their original tables.

I though I did wrong the calculations.

For the individual tables it was simple: 

COMP1_recs = COUNTROWS(COMP1Table)

For the combined table, I have used COUNTROWS filtering on the TAG column:

COMB_COMP1_TAG = CALCULATE(COUNTROWS(COMBTable),COMBTable[BU_TAG]="COMP1")

Similar formula for the other tag.

But it seems is NOT the calculations either because I have tried to reproduce the problem using two Excel files and the result were correct even if I use a "parameter" for the folder.

I have tried with other two tables using the same OData connector, and the problem persists - wrong data after appeding tables.

So I suspect it could be the OData connector?

Or what else am I doing wrong?

Kind Regards,

Lucian

[v-xuding-msft]

Hi @Lucian ,

Based on your description, the issue seems to be caused by the odata connector. Then I create a simple sample and connect the data source manually not with parameters. But it works fine. The attachment is my sample, please try on your side to check if it works.

Data Source:

http://services.odata.org/northwind/northwind.svc

 Result

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lucian]

Hi @v-xuding-msft,

And thank you for your help. Indeed it worked, even with two different tables as in my original scenario will return the correct number of items.

What I realized is I might mislead you mentioning the "OData" connector. In your sample the source is indeed an OData connector, when in my case is Dynamics365BusinessCentralOnPremises (the connector's URL is ending with "ODataV4" 😁).

Anyway it seems that is a connector related problem so it would be helpful if someone could test the scenario with Dynamics365BusinessCentralOnPremises connector.

Kind Regards,

Lucian

[Lucian · Accepted solution]

Hello,

It seems that the problem was solved if I've added for each individual table an "index column". Now it seems I have proper results in the combined table.

So could this be considere a "bug" or a "feature"?... 😁

Kind Regards,

Lucian

[v-xuding-msft]

Hi @Lucian ,

Maybe it is caused by your actual data structure? Anyway, I'm glad that you have resolved the issue. 😊

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.