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

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.

Reply
Dmclellan1
Regular Visitor

Separating out values in power query based on same unique identifer

Hi team,
In power query I have 2 columns. memberId and display Value. Here is a sample of the current data:

memberIDdisplayValue
8897Cool
8897Not Cool

I want it to muniplulate it to look like:

memberIDdisplayValue1displayValue2
8897CoolNot Cool

 

I have tried multiple methods and keep getting stuck with the same layout. Simple steps would be greatly appreciated. 
Thanks in advanced! 🙂

1 ACCEPTED SOLUTION

 

Ok. So, the very simplest way to solve your exact issue is to create a column that has your new column headers in it, then pivot that column:

// New custom column 'ColumnHeaders'
if [displayValue] = "Cool" then "DisplayValue1"
else "DisplayValue2"

 

Pivot column:

BA_Pete_2-1708584569962.png

Output:

BA_Pete_1-1708584442324.png

 

Obviously this is largely dependent on how many actual DisplayValues you have per MemberID, and how frequently the values change.

 

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

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @Dmclellan1 ,

 

Can you provide a bit more context around your scenario please? Is this a fact table, a dimension table, do you need to perform calculations on it later etc.?

Whilst this transformation is possible, it may not be the right thing to be doing. Remember, Power Query isn't meant to get data into a visually-pleasing format/structure, it's meant to get data into the most efficient format/structure.

 

Pete



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

Proud to be a Datanaut!




Hi @BA_Pete,

Thanks for the reply.

This data will be displayed in a table format. The data is being imported from a web api that is seperating the recrod based on the import method but these fields (cool and not cool) are actually unrelated and should be attached to the same record. I will need to do the same thing for multiple fields from the import. If I understand how to munipulate this one thes rest should fall into line.

 

Ok. So, the very simplest way to solve your exact issue is to create a column that has your new column headers in it, then pivot that column:

// New custom column 'ColumnHeaders'
if [displayValue] = "Cool" then "DisplayValue1"
else "DisplayValue2"

 

Pivot column:

BA_Pete_2-1708584569962.png

Output:

BA_Pete_1-1708584442324.png

 

Obviously this is largely dependent on how many actual DisplayValues you have per MemberID, and how frequently the values change.

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors