Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHolding CTRL helped a lot. I played around with it and it works fine. Thanks a lot!
Hi dude,
Once u merged that two columns , Remove Duplicate thats it.
Try this.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |