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.
I have some data that I need to pivot and am at a bit of a loss. Maybe it is the quarantine or maybe my brain it in just too much of a fog. Regardless... my data looks like:
Node | Tag |
LINE4 | 1ST_SHIFT_PG |
LINE4 | 2ND_SHIFT_PG |
LINE4 | 3RD_SHIFT_PG |
LINE2 | 51-136-FLT |
LINE3 | 51-136-FLT |
LINE4 | 51-136-FLT |
LINE1 | 51-136-FLT |
I need to be able to pivot the data like this:
Tag | LINE1 | LINE2 | LINE3 | LINE4 | LINE5 |
1ST_SHIFT_PG | YES | ||||
2ND_SHIFT_PG | YES | ||||
3RD_SHIFT_PG | YES | ||||
51-136-FLT | YES | YES | YES | YES |
Now - it doesn't hae to show YES. Just needs to show tag-by-tag and what Node it is associated with.
Appreciate any help that will get me out of my brain-funk.
Solved! Go to Solution.
Hi,
Create a simple Pivot Table.
Hi,
Create a simple Pivot Table.
Thank you! That worked pretty easily 🙂 I still used Power Query (not BI) to get the data into the format that I needed, but this got me to the final step.
Now - I loaded PowerBI. But I do not see any sort of Matrix Visual option anywhere. Would be nice to have a look at that also just to broaden my knowledge.
You are welcome.
I think you can leave the data as it is.
Just use a matrix visual, pull Tag on to rows, Node on to columns, and create a measure for COUNT(Tag) and put that in Values.
The problem is that it would take a long time to figure it out 'as is' from the perspective of what needs to be accomplished. The consumer of the data will need to be able to look at each TAG and then decide which AREAS are appropriate for the data. To look at it 'as is' would take a very long time and they would really have to manually convert it to the way I asked above anyway 😞
I'm not sure why the table squished everything together up there either. Looks funky after it posted.
@HotChilli is right. Here is what it looks like when you follow that suggestion. Your example data didn't have Line5 so it doesn't match your output exactly.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I think I see my problem now. I have Power Query, not Power BI 😞 So - essentially only Excel 365 for Business. I don't see the Matrix Visual in here - so assume it is part of that package and realize now I'm in the BI community 🙂
There may be a slight misunderstanding. I mean there won't be any need to transform the data in Power Query.
Use a matrix visual and follow my suggestion. Let me know how you get on.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |