Problem Appending Queries from NAV Data Source

[Anonymous]

Hi all,

I'd really appreciate some help on the following issue if possible.

I'm currently pulling 4 different entities data from the NAV data source (OData link) and then trying to append the tables together to create one table containing all the data I need.

All 4 queries are working fine, pulling in the correct number of rows when checked back to the source, however, the problem arises when I try to append the tables together. In my head this should be a very straightforward process of lumping 4 tables on top of one another where all sources contain the exact same columns.

Unfortunately this is not the case... the append seems to take the data from the first table and multiply it 4 times (with each new duplicated set of data containing a different entity name)... See below for some screenshots:

Ledger D (Sample of 4 rows but full table contains 3,451 rows)

 

Ledger C (Sample of 4 rows but full table contains 4,751 rows)

 

Ledger B (Sample of 4 rows but full table contains 845 rows)

 

Ledger A (Sample of 4 rows but full table contains 172 rows)

 

In my append I would expect to recieve 9,203 rows of data (all 4 tables combined together), however...

I recieve varying results like 3,380 rows (Ledger B multiplied by 4 where the full Ledger B is duplicated 4 times, however, the Entity is different for each of the 4 duplicatations). I also sometimes receive results of 2,034 (Ledger B + Ledger A multiplied by 2 where the full Ledger A and B entries are duplicated, however again the entity is different for each replication of the dataset).

Has anyone ever come across this before? I can't get my head around it at all.

For info; there are no relationships built between the 4 tables and the "Key" field (an applied step in Power Query after the data is pulled from source) is completely unique across the combined datasets. The "Entry_No" field (comes directly from the source) is unique in each separate table, but has duplicates when the tables are combined.

Any help on this would be much appreciated!

Thanks,

Aaron

@amitchandak @Mariusz @Greg_Deckler @mwegener @MattAllington @MFelix 

[mwegener]

Hi @Anonymous ,

how is the entity different for each of the 4 duplicates?

[Anonymous]

Hey @mwegener 

Thanks for responding so quickly! The entity is different for each of the sources, for example:

I then added a custom column with, in the example above, "LedgerA" as the "Entity" field.

Hope this helps!

Aaron

[mwegener]

Hi @Anonymous ,

can you post a screenshot of the append dialog?

[Anonymous]

@mwegener of course!

Cheers,

Aaron

[mwegener]

Hi @Anonymous ,

can you post a screenshot of the duplicate rows?

With identifier column and possibly different column values.

[Anonymous]

Hey @mwegener 

Its difficult because of the size of the dataset, and i'd also have to anonymise the data, however this person is facing the exact same problem as me and their screenshots are quite a good way to see it (albeit they are merging 2 tables instead of 4). I believe they are also extracting data from NAV.

https://stackoverflow.com/questions/59249763/append-2-queries-result-in-repeating-of-data-from-first...

Does this help?

Aaron

[mwegener]

Hi @Anonymous ,

i'm not sure if entry no does not exist in both queries.
But try to isolate the result of the individual query with Table.Buffer and then to append.

https://docs.microsoft.com/en-us/powerquery-m/table-buffer

[Anonymous · Accepted solution]

Hi @mwegener 

I tried to use Table.Buffer like you suggested, however the problem still existed.

Upon investigation, all tables were going to a single OData link and therefore the last table each time was replicated by the number of tables. Not really sure why this was happening, but I managed to fix the problem by using a "OData Feed" source instead of the "Dynamics Nav" source.

This allowed me to create a single source for each table which correctly feeds the append.

Thanks so much for your help though! Hope this info is of some use if you come across the issue again!

Aaron

[mwegener]

Hi @Anonymous,

glad to hear that. Please give Kudos for support.