How to merge more rows to one dependent on unique value

[Libor]

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

[Libor]

Hi,

thank you very much for the instruction.

It works as I expect.

Libor

[ERD · Accepted 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.