To Pivot or Unpivot? That is the question!
Вставка
- Опубліковано 3 жов 2024
- 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
docs.microsoft...
Unpivot columns
docs.microsoft...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #PowerQuery #GuyInACube
Yes, this is on the PL-300 exam (unpivoting and RLS filters requiring bi-directional filters).
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.
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.
Alan, I completely forgot about the feature. Thanks for pointing it out.
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!!!
THANK YOU! This was really helpful.
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
Jonathan, I completely forgot about the feature. Thanks for pointing it out.
@@GuyInACube Hi, no worries, hopefully this isn't the only thing people take away from the video - looking at the other comments! :)
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 options.....
I was expecting him to do that ..
Thanks, completely forgot about that.
Power Query is an amazing tool to build a proper star schema and complement your ETL process smoothly.
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.
Hey Patrick, In the 1:25 step, I usaly do:
= Table.TransformColumns(#"Reordered Columns", {{"Listofilds", each Text.Split(_, ","), type list}})
And then Expand the column.
Isn't this way more efficient?
Thanks!
I was thinking exactly the same thing. It could be worth fot Patrick to make a video to benchmark those two M code approaches ? Thanks
@@Anthony_Lecoq yes I agree that video idea would be great and maybe it can be more like how to efficiently combine multiple "M" queries
This is awesome. Thank you
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.
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🤗
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
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!
Great example!
Wow, great helpfull with few words and short time!
Thanks! Very useful 👍
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)
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.
Nice and quick one!
I wanted to comment but I see that BiEx already "stole" my thought :) For sure the benefit of what BiEx suggests is (apart from efficiency) that you don't introduce the part of code which Patrick does, that might cause a missbehavior in next refresh.
The thing with Split Column by Delimiter is that you hardcode the number of columns you want to create with split column. Unless you count how many commas you have in a cell, and take a max from entire table, and plug it as a paremeter into split column function.
Please explain slowly for the next video as I got lost half way through 😥
lol I had to check my youtube settings to make sure it was at the default speed
Is it better to duplicate or reference a query?
Do or do not; there is no try.👍Right On!
Nice one mate as usual but why were you speaking at a speed of 🚄.
Can it possible to use Dax script to do the task.
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?
1:53 But I thought you just UNpivotted!
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.