Power Query - Help with pivoting my data like...

[Covington]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Create a simple Pivot Table.

[Covington]

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.

[Ashish_Mathur]

You are welcome.

[HotChilli]

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.

[Covington]

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.

[mahoneypat]

@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

[Covington]

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 🙂

[HotChilli]

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.