Forum Discussion
Solution SageMerging issue where a line gets doubled when pulling in field from merged query
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?
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.
8 Replies
- MFelix
Super User
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.
- MFelix
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.
rpinxtOh 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