Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

Concatenation of 2 column

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_visitreportidUser 1User 2User 3User 4User 5User 6User 7
XXXXXXXXXxxxxxxx
XXXXXXXXXxxxxxxx
XXXXXXXXXxxxxxxx

 

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 !

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi, 

 

Thank you very much it works like a clock ! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors