Misterious duplication in Power Query transformation output

[hz4wj5]

HI All,
i googled the net in the past few days to find something useful to my recent problem without any result, so i try it now here.

I have a dataset (5000+ rows, 50+ columns) coming from a server that is transformed by my powerquery: while keeping the rows (ie. the records) adding/deleting columns to transform the output easy-to-read for my users. Each record/row represents one specific unique product (so by definition each item appears only once in the dataset.)

Recently i just realized that there are two records that are duplicated in the powerquery result and i cannot figure out the reason. I checked the source file and in that there is no duplication. Is there any good reason why this is happening? I use only one file as source (ie. do not append two lists for example that could potentially result in duplicated records).

First, i tried to walk through step by step in the query editor in which step the duplication appears without any result because: 1. it is not possible (?) to search in the query editor window and 2. it anyway shows only the first 1000 records (thus searching in the drop-down filter does not work either)...so doing this kind of detective work is not really possible/feasable

Is there any transformation step that could potentially result in duplication of a record?? Again, what i do in the power query steps is to transform the information in the columns by using power query functions, transformation methods or merges from external lookup tables

Any idea, hint is welcomed and much appreciated! Thank you!
hz4wj5

 

[Matt Allington]

No, Power Query cannot duplicate records unless you append (as you suggested). I would
1. load the data as an Excel table
2. sort by the ID key
3. find the duplicate(s)
4. go back to power query and apply a temp filter on that key to see what is happening.

 

[hz4wj5]

Hi Matt, thank you for the feedback. I already know those two items that are duplicate. HOwever if i go to the query editor then i cannot sort them by using the autofilter because it says "Limit of 1000 values reached" (and apparently those two items are both in the "beyond" range). Therefore i was thinking to make the walk through step by step - from the beginning - the "Applied Steps" and see where the duplication is born, however having no quick possibility to search the window it is cumbersome: ie. there is no like Ctrl+F (at least i do not know about it) to quickly check the table after each applied step is re-performed.
As far as i know there is the possibility to apply a "Remove Duplicates" step however that does not resolve the problem why the duplicate at all has been created...

 

[hz4wj5]

UPDATE: looks like i found the root cause. After comparing the duplicates in the excel table (column by column) it turn out that in one of the external table - that i made merge (~vlookup) operation from - those two items were duplicated and took up two different values. Surprisingly (to me) as a result in power query the items were multiplied in order to show them with both value...for me this is kind of strange/unexpected behaviour...