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.
Hello,
I would like to ask for help with merging more rows to one.
I created connection from Sharepoint List to PBI. Everything is ok, but if there is a record with more then one value in the field, this record is split to more rows. E.g. in the SharePoint list is row with ID-1, where is a field "Responsible Persons". In this field are two users - user1 and user2. After loading this record (ID-1) to the PBI, it is split to two rows - both have same ID (ID-1) but users are different in the collumn "Responsible persons" - first row contains "user1" and second row contains "user2".
I would like to have these Responsible persons on the same row, like this:
Because otherwise it counts the same ID multiple times.
Is there any way, how to merge these two row to one? Something like "find same values in selected collumn and merge whole rows"?
Of course, there is another way - just left only the first record with first Responsible person (user1) and remove others, but if it is possible, I would prefer to keep all values. I also do not know, if removing suits for me. The list is active and it changes over the time. So, if I remove these duplicate record in the PBI and create new record in the Sharepoint list (again Responsible persons will be more then one person), do I have to clear the data again to get only one value after loading to PBI?
Hope it is clear.
Thank you in advance.
Libor
Solved! Go to Solution.
Hi @Libor ,
You can merge the values in Power Query.
For example, if we have a table
Then add step (group by 2 columns):
= Table.Group(Source,{"Date", "Column2"},{{"Column1", each Text.Combine([Column1],","),type text}})
We get the next result:
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi,
thank you very much for the instruction.
It works as I expect.
Libor
Hi @Libor ,
You can merge the values in Power Query.
For example, if we have a table
Then add step (group by 2 columns):
= Table.Group(Source,{"Date", "Column2"},{{"Column1", each Text.Combine([Column1],","),type text}})
We get the next result:
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |