Merge multiple rows

[DimaLnk]

Hi,

I'm a relatively new user to PowerBI and I really find this software amazing. However, I'm kind of stuck with one issue that I need help with.

I have a folder as a source (with about 3000 CSV files), and everything works fine.

Now the issue is that some of the CSV files contain a "unique ID" multiple times, and I would like to merge them into one row. However, mostly the value of almost all attributes contain the same thing, and I only need it to merge the values of a specific attribute. (preferably the ones without a blank value)

UniqueID	Attr1	Attr2	Attr3	Attr4
Unique1	AAA	123	789
Unique2	BBB	123	789	111222333
Unique3	BBB	123	987	222333444
Unique1	AAA	123	789	444555666
Unique4	AAA	123	456	555666777
Unique2	BBB	123	789	111222333
Unique1	AAA	123	789	444555666

 So the idea is to have for Unique1 the Attr4 mergerd

However, for Unique2 the Attr4 is the same, so should should remain also the same.

 So the desired result would be:

UniqueID	Attr1	Attr2	Attr3	Attr4
Unique1	AAA	123	789	444555666
Unique2	BBB	123	789	111222333
Unique3	BBB	123	987	222333444
Unique4	AAA	123	456	555666777

 Is there any solution with PowerBI for my problem?

Kind regards.

[MFelix · Accepted solution]

Hi @DimaLnk,

If I understand your issue you have the same ID with all the same values except in some rows that have blanks, this can be sorted by doing a group by in the query. Based on you example you need to do a group by UniqueID and Attr1 (both are text fieds), then do the max for the other 3 attributes, this should work as you need, considering that the lines are always the same if you have different values per UniqueID the Max may not work as you need.

Please check if this works for what you need.

Regards,

Mfelix

[DimaLnk]

Hi @MFelix,

Thank you for your reply.

It looks closer to the solution I need, however, my acutal table has 50+ attributes, and that would mean that I would probably thus need to add the separatly in the group by (as doing a group by ID and attr4 as value, discards all the other attributes).

I could of course add it a separate query and then link them to each other, but that also looks like a "dirty solution".

It's a partial solution, but I'd like to know if there wouldn't be a better //futureproof// solution?

BTW: Attr4 is actually a text-field, but it looks like MAX actually works on it 🙂

Kind regards.

[MFelix]

I don't know how the full model is set-up because you only presented part of the layout however you can select multiple columns before doing the group by (use CTRL) if the blanks are only in certain atributes you can use this option to select all other columns an then select the rest in the bottom part of the screen.

If you want me to test something different I can do it with a sample of the data.

Regards,

MFelix

[DimaLnk]

Holding CTRL helped a lot. I played around with it and it works fine. Thanks a lot!

[Baskar]

Hi dude,

Once u merged that two columns , Remove Duplicate thats it.

Try this.

[DimaLnk]

Hi Baskar,

The issue is that I need to merge rows, not columns. The idea is also that it happens automatically without any manual interaction (except refresh data)