Concatenation of 2 column

[Syndicate_Admin]

Hello guys.

I'm actually blocked with my problem and can't find a solution that i understand with actual level on Power Query.

As you can see, i have a table that contains ids. In the cms_visitreportid, you can find at times the same id.

My goal is to have one unique line for each visitreportid but with all user_representativeid like below :

cms_visitreportid	User 1	User 2	User 3	User 4	User 5	User 6	User 7
XXXXXXXXX	x	x	x	x	x	x	x
XXXXXXXXX	x	x	x	x	x	x	x
XXXXXXXXX	x	x	x	x	x	x	x

PS : In the table above, the "x" are representing "cms_cms_visitreport_user_representativeid" for each "cms_visitreportid" line. A single Visit Report can have a multitude of users so i can't just give a maximum number of columns.

Thank you in advance !

[BA_Pete · Accepted solution]

Hi @Syndicate_Admin , @Whyppie

First, add a column that maps the [cms..representativeid] to the column names that you want to end up with.

You will probably want to merge a reference list to your table to achieve this.

Once you have this new column, select it ad go to Transform tab > Pivot Column.

Select [cms..representativeid] in the 'Values Column' dropdown.

Expand Advanced Options and set 'Aggregate Value Function' to 'Don't Aggregate'.

Pete

[Syndicate_Admin]

Hi, 

Thank you very much it works like a clock !