To Pivot or Unpivot? That is the question!

Рет қаралды 36,216

Күн бұрын

Пікірлер: 40

@jaguarprovo 3 жыл бұрын

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.

@SaveThatMoney411 Жыл бұрын

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

@KaranArora227 3 жыл бұрын

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!!!

@asjones987 3 жыл бұрын

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.

@GuyInACube 3 жыл бұрын

Alan, I completely forgot about the feature. Thanks for pointing it out.

@biexbr 3 жыл бұрын

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!

@Anthony_Lecoq 3 жыл бұрын

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

@VishalJaiswal-jj3ke 3 жыл бұрын

@@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

@estebandiaz4405 Жыл бұрын

THANK YOU! This was really helpful.

@JJ_TheGreat 2 жыл бұрын

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.

@Narses3 3 жыл бұрын

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

@GuyInACube 3 жыл бұрын

Jonathan, I completely forgot about the feature. Thanks for pointing it out.

@Narses3 3 жыл бұрын

@@GuyInACube Hi, no worries, hopefully this isn't the only thing people take away from the video - looking at the other comments! :)

@christyanho 3 жыл бұрын

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.

@sjames1958 3 жыл бұрын

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.....

@premcst 3 жыл бұрын

I was expecting him to do that ..

@GuyInACube 3 жыл бұрын

Thanks, completely forgot about that.

@muhammedahmedhashmi2049 8 ай бұрын

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)

@wmuflyboy22 3 жыл бұрын

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.

@SQLSekou 3 жыл бұрын

Is it better to duplicate or reference a query?

@LARflixMovies Жыл бұрын

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

@mostafahwafy 10 ай бұрын

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🤗

@Somnath_Explorer_01 2 ай бұрын

I just saw your video, but when I try it for the scenario below, it's still not working. Situation: For example, there are columns "Date", "Value" and "Product ID". so I used 'unpivot columns'. date will be in rows and the Product ID and Value will also be in rows. once it is done I wanted to make the relations between other tables with the "date". but it's not working. any idea how to fix this? Or is it really possible to create a relationship with the dates based on one being in columns and the other in rows? Also, if this is possible which chart shall I use to identify the values with each product ID over the date time line. Wants the line chart actually. Can you advise?

@luisalejandrorodriguezcamp9516 3 жыл бұрын

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

@anand29091987 3 жыл бұрын

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?

@NicBob89 3 жыл бұрын

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!

@dhritijitsengupta6804 2 жыл бұрын

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.

@WongibeAlain 3 жыл бұрын

This is awesome. Thank you

@pawewrona9749 3 жыл бұрын

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.