how to detect easily in which step i generated duplicated row in power query

[Gustavo98]

Hi everyone @edhans 

I commited a mistake and i generated duplicates rows, how can i detect easily in which step is my problem?

I would like to see in the image above the real rows number not the 999+.

Ty very much

[edhans · Accepted solution]

One if the ways to do this inadvertently is with a merge. Say you have data that looks like this in your merge. Notice in the 2nd table the #3 is duplicated:

When you merge, it will look like this:

But when you expand the merged column, it will look like this, duplicating the records on #3 because there were two of them in the right table.

The way to prevent this is to make sure the table on the right side of the join has a 1:1 cardinality with the left table.

There may be other ways to cause this, but merge is the most common.

[BA_Pete]

For future readers, just to crank this up a notch, let's say you have a 100-step query with 40 merges (I know, right!), and you just want to see your row counts change over the entire thing without too much effort. Well then, give this a whizz:

-1- Duplicate your query

-2- Open Advanced Editor on your copy and change the 'let' to '[', and your 'in lastStep' to ']'. So:

let
    Source = mySource,
    someStuff = doTransformMagic
in
    someStuff

...goes to:

[
    Source = mySource,
    someStuff = doTransformMagic
]

This will convert your query into a record, with each step being its own row, something like this:

-3- Convert your record into a table and add a custom column that is just = Table.RowCount([Value])

Et Voila:

Then, to update later when new steps are added, you can just copy your current query between let and in, and paste it into your copy between [ and ].

Pete

[Gustavo98]

This is amazing, ty vry much Pete

[BA_Pete · Accepted solution]

Hi @Gustavo98 ,

I agree with @edhans , row duplication most commonly happens during merges.

However, to check the row counts at each step, the number at the bottom isn't going to help you.

Select your Navigation step from the APPLIED STEPS list and go to the Transform tab > Count Rows. This will just output a scalar value of your row count as at the prior step.

Then you can just drag and drop your row count step down through the step list to quickly(ish) see the count at each stage.

It's a back of a tedious hack, but probably the quickest and simplest method.

Pete

[Gustavo98]

Ty for your answer, nice tip, i was using that but not draging it up or down as you said, this have a lot of sense and will help me to save time

TY

[edhans · Accepted solution]

One if the ways to do this inadvertently is with a merge. Say you have data that looks like this in your merge. Notice in the 2nd table the #3 is duplicated:

When you merge, it will look like this:

But when you expand the merged column, it will look like this, duplicating the records on #3 because there were two of them in the right table.

The way to prevent this is to make sure the table on the right side of the join has a 1:1 cardinality with the left table.

There may be other ways to cause this, but merge is the most common.

[Gustavo98]

Ty vry much, Your explanation is helping me a lot !

[edhans]

Glad I was able to help @Gustavo98