To Pivot or Unpivot? That is the question!

Sometimes your data isn't shaped the right way. Have you tried the Pivot or Unpivot methods in Power Query? They can help get your data looking the right way for your Power BI reports!

Pivot columns

Unpivot columns


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #PowerQuery #GuyInACube

[asjones]

I love the ability to unpivot stuff in Power Query. However you may need a better example. Split by Delimiter then Advanced ... into Rows is cleaner and probably faster and something I have done a few times for other stuff.

[SaveThatMoney]

Yes, this is on the PL-300 exam (unpivoting and RLS filters requiring bi-directional filters).

[jaguarprovo]

Needed this!! Multiple times I’ve run into an issue where my filtering issues are caused by not really understanding how to properly use the roles feature and how this directly impacts my models performance.

[KaranArora]

This was exactly what I was looking for so many days. Thank you so much. It will save me a lot of time in office from now on. And the the thumbnail to this video is superb!!!

[biexbr]

Hey Patrick, In the 1:25 step, I usaly do:
= Columns", {{"Listofilds", each Text.Split(_, ", "), type list}})
And then Expand the column.
Isn't this way more efficient?
Thanks!

[sjames]

Hi...

Split column by delimiter can go straight to rows instead of split to columns then pivot to rows.

Have a look at the advanced

[Narses]

Nice quick proof of concept showing RLS, quick note - there's no need to "pivot or unpivot?", you should have split by delimiter into rows
Nothing fancy all in the UI

[christyanho]

Is the bidirectional bridge better performing than a simple single-direction M:M relationship? Asking because I seem to get better performance using the latter option but I can't say I could test on a controlled environment.

[wmuflyboy]

I started using unpivot all the time this year at work. My company has this nasty habit of calendarizing everything horizontally (excel files), which doesn't visualize well in Power BI.

[edgards]

Power Query is an amazing tool to build a proper star schema and complement your ETL process smoothly.

[JJ_TheGreat]

1:40 So how do we know that we need to unpivot vs. pivot - and how do we know what columns to unpivot or pivot?
Thanks.

[muhammedahmedhashmi]

Hi @GuyInACube, I have a sharepiont list as table and When I tried to "Unpivot Only Selected Columns" then it is not creating seperate table as I can see in your screenshot. it is unpivot rows in the same dateaset which means rows increased by (No of rows * unpivot columns)

[klgyal]

Please explain slowly for the next video as I got lost half way through 😥

[mostafahwafy]

could you answer me on this question plz:
i want to highlight top 3 in pivot chart with every drill down is that possible ?
thank you for all your information you sharing🤗

[SQLSekou]

Is it better to duplicate or reference a query?

[luisalejandrorodriguezcamp]

What I hate about unpivoting like this is that the number of columns gets hardcoded. If there is more IDs per person they are ignored

[NicBob]

This is a pretty sweet trick. I wish they had a generic split function in SQL... I had to build one for a project I had... but it's super inefficient because it needs to dynamically count the number of columns based on the delimiter and then inserts per record via a loop... because the splitter function only works per record... can't do the full table in one go. But this is super sneaky!

[LARflixMovies]

Can it possible to use Dax script to do the task.

[anand]

Can't we write a single sql query which will implement all those logic as you showed and get data in power bi? will it slow down the refresh?

[dhritijitsengupta]

Hi Patrick,
I have a question. What if I have 5 such metadata columns that I don't want to unpivot and some of them are numeric in nature but I have other columns which needs to be unpivoted.
Once I unpivot the other columns and I try to calculate average of one of the numeric (unpivoted) column. I get two issues. I don't get the correct average and also the average doesn't get filtered by my unpivoted (attributes) column.
I have tried everything that I could but yet to succeed.