Forum Discussion
Power Query - Help with pivoting my data like...
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.
Hi,
Create a simple Pivot Table.
8 Replies
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.
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.
