Merging issue where a line gets doubled when pulling in field from merged query

[rpinxt]

I have this simplified setup in power query editor:

Two runs, one at 10 and another at 10:30.

Now first run the delivery had status To Pack and second run it had status Done.

And the delivery has only 1 unit on it. So very simple.

I merged it on the key that I made from Delivery - Material - Batch. There was a 100% match between the 2 sources.

You see here that even after the merge it still only show 2 lines which is correct.

But when I pull in from the merge the field (Storage Type) that I need.... :

Twice as many rows all with 1 unit but still it doubles of course when you use the field in visuals:

Both lines should have 1 unit of course because the delivery has only 1 unit at any moment of the day.

I put the small sample file on Google Drive. Anybody with this link shoud be able to see it:

https://drive.google.com/file/d/13C1xfJSiwnuhBYzVdEZB51rRy-U9tPCo/view?usp=sharing

Can anybody explain to me what is going on and how to fix this?

[rpinxt]

Hello @MFelix thanks for you support.

Well yes I am merging on 1 field but this field is a key field consisting out of 3 other fields.

Anyway I now also uploaded the excel file to drive:

https://docs.google.com/spreadsheets/d/13ETjZ8UR-SZb5lwqqZML0qJrmUO3LOKv/edit?usp=sharing&ouid=11190...

[MFelix · Accepted solution]

Hi @rpinxt ,

Since your keys are matching for all rows you get more than one line for each one:

When you expand you get two rows. In this case you need to select the aggregation and not the expansion:

One row per each one

Be carefull on this merge since if there are for the same key more than one Storage type you will only get the maximum, you can also select the minimum or make it in a different way.

[rpinxt]

Oh my I was not aware of this behavior. Still not sure if I understand why it gives 2 rows when normal expanding. There is only 1 line in the lookup with that key. Or is the timestamp (runtime) the one causing the doubling?

This seems indeed to work. Now hoping it will work on the main file to.

Thanks @MFelix 

[MFelix]

Hi @rpinxt ,

There are two rows because on the merge table you have two rows that have the same key so it returns the full match between the other table:

whe  you do the merge you are picking up this two rows that have the same value that is why when you expand it you will get the duplicate of the row one for each of the times.

That is visible on my first screenshot on the other post.

Glad it could help.

[rpinxt]

Yes you are correct @MFelix 

And these 2 rows come from the RunTime. For next run you get 3 rows etc.

Your solution worked but unfortunately not for me.

First of this aggregate is really slow as it needs to calculate for every row.

Not that much rows but I could see it going one by one.

And when done it said I could not use this step in directquery.....

So bad luck for me. But it did set me on the correct path.

Having the key every runtime was the problem.

Luckily I not needed runtime on the second source.

It serves merely as a lookup so I only needed the latest version.

So now I overwrite everytime the last version and therefore my key is only in once 😄

Solved my problem!

Thanks again for pointing me in the right direction.

[MFelix]

Hi @rpinxt

Another option can be to generate an aggregated table that is only use ffor the lookup that will group the keys and return that column value and then merge it with your final table 

[rpinxt]

Yes could probably also done that. But now I was able to configure the source (removing the column with the timestamps). So no extra steps in query editor.

[MFelix]

Hi @rpinxt ,

Since we do not have the base file (Merge doubling issue.xlsx) I can check the merging however what I can see is that when you do the merge you are getting more than one value since you are making the merge based on a single field so you get not only the Done or To pack in each line of the merge but you are getting all of them.

In this case you need to force the merge to be done in a one one line.

If you can share the sample XLSX I can try and give you a better answer.