Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi team,
In power query I have 2 columns. memberId and display Value. Here is a sample of the current data:
memberID | displayValue |
8897 | Cool |
8897 | Not Cool |
I want it to muniplulate it to look like:
memberID | displayValue1 | displayValue2 |
8897 | Cool | Not Cool |
I have tried multiple methods and keep getting stuck with the same layout. Simple steps would be greatly appreciated.
Thanks in advanced! 🙂
Solved! Go to 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:
Output:
Obviously this is largely dependent on how many actual DisplayValues you have per MemberID, and how frequently the values change.
Pete
Proud to be a Datanaut!
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
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:
Output:
Obviously this is largely dependent on how many actual DisplayValues you have per MemberID, and how frequently the values change.
Pete
Proud to be a Datanaut!
User | Count |
---|---|
19 | |
10 | |
10 | |
9 | |
7 |
User | Count |
---|---|
23 | |
18 | |
16 | |
13 | |
11 |