Transpose, pivot or unpivot in Power Query?

[@valenmolina]

I've been struggling for hour with a table that was given to me in the wrong format and with this video I could fix everything and get started on my report. Thanks a million!!

[@CurbalEN]

🎉

[@joangarcia6327]

La mente humana es algo maravilloso: hace unos dias vi este video sin entender demasiado. Hoy me encuentro creando un informe, en una tabla que no quedaba bien y.... zas! “Ese video....” lo he vuelto a ver y aquí estaba la solución.

[@Tony-uv4pd]

Thank you for the simple explanation. Sometimes, it can get confusing. But this video is a great reference!

[@imconfused1237]

Ruth, thank you sooo much for demonstrating how to use these techniques. It has instantly solved a huge headache, where our Data Model had to monthly injest 20k rows. Using this, I am now able to: pivot the data at source, extract as a PDF, import into PowerBI and then use the 2nd technique to transpose + unpivot it back into a useable dataset. Absolutely superb!!!! 👍

[@CurbalEN]

🥳🥳🥳 /Ruth

[@kiddwong4186]

This is exactly the video I needed to help me reconfigure some data I had in a rubbish format. Thank you so much!

[@CurbalEN]

Wonderful to hear!!

[@siyabongamyeza5315]

This is a very deep concept. Thanks for making it short and straight to the point

[@bibekanandabhattacharjee952]

I understood just the definition. However, thanks @Curbal for igniting the interest in me.

[@Pedro_Israel]

Didn´t know how to put into words what I was looking for: "Unpivot" is the answer. Thanks.

[@CurbalEN]

Yeah, the first hurdle is always to know hoe to search. Once you know that, the rest is easy ;)

[@carltonseymour869]

Thank you, Ruth. This video solved a nagging issue a colleague was trying to soolve.

[@enochs4290]

Thank you! Just the first examples you showed in the first minute answered my questions!!!

[@CurbalEN]

Excellent!

[@tianag4627]

At 4:13 when you removed Change Type, what did you click exactly?

[@adityapuro5654]

Curbal, your content helped!! Thank You! Keep Growing!

[@raniakkina4792]

Thank you for explaining things so clearly. This concept always used to confuse me.

[@JJ_TheGreat]

1:33 So would it be possible to do the same thing WITHOUT transposing? If so, how would it be done? Thanks.

[@ReisdoBI]

It was one of the most helpfull things I watched in youtube this year! Thanks a lot! Abraços do Brasil!

[@CurbalEN]

Delighted to hear :)

[@Gustavo-Santana]

Thanks for the great content :), for Q2 I believe the answer is number 4: Microsoft Azure SQL Database

[@CurbalEN]

Thanks Gustavo, I will reveal it on Friday :)

[@ericdowdy3609]

Great explanation on 3 key tools in power query for shaping data! Thanks Ruth!

[@CurbalEN]

🥳🥳

[@joat9105]

you save my life, my god this is very helpful thank you very much mrs

[@lelouchl3263]

thank you so much. it is one of the easiest one to understand out of all the other ones I have watched on youtube. Again, thanks.

[@CurbalEN]

Pleasure and glad it was helpful:)

[@MANDEEPSINGH-bj9hq]

That exactly what I was looking. Thank you!!

[@eshwarkotha899]

Many thanks for this valuable session I have understood clearly about transpose pivot and unpivot and very useful topic and these topics will be implemented on my work

[@CurbalEN]

I use them all the time too :)

[@aminzakariah2891]

Love your content! Quick and concise

[@CurbalEN]

Thanks!

[@mikesmith2315]

thankyou for this. The best explanation i have seen.

[@coolname-pf2xh]

Excellent. Thank you so much for clearly explaining with concise examples. You rock.

[@skipa9906]

Thank you for your explanation. I have a question, what if i just want to take one row and turn it to a column?

[@endingofworldmovies798]

at the end how the last column sales come up ?? before it was not there right ?? how come 3 columns turned into 4 ?

[@rmmccarthy1240]

Thank you. This has helped me a great deal.

[@Happymen-u]

How can I make any chart or visual based on this format like I want to create line chart for company A and B based on months

[@renatofuenteslespinasse7341]

I love you Curbal, u are the best:))))

[@CurbalEN]

You too!!

[@64BitTeluguGaming]

simple and clean thank you

[@ChavaCar11]

hey got a question with the Pivot/unpivot functionality. Hope you can read this and answer, I got a system that downloads forecast into weeks, months, etc. but it is always rolling data, so if I do it weekly, current week (which would be week #1) is no longer in next week, as the second week becomes first week and last week is replaced by the one following and so on so forth. Format is always time stamp as column, so I have a part and then 13 weeks or so one column per week, 13 columns of timestamps with qty. My issue is that no system I've used likes missing columns, it likes new ones to be added but not taken out of the data source, if I unpivot the the data to be rows instead, am I going to get the same issues when a column is missing or this overrides that and takes it as it is rows and nothing happened?

[@luisfernandomacedo1451]

Hi, many thanks for sharing this video! I have applied unpivot to my table, however some values from the other columns are kinda going ghost. What's happening? Do u know ?

[@osoriomatucurane9511]

Awesome tutorial, concise and crystal clear. When we reshape the dataset by unpivoting the values in columns to rows, we get a long format where other variables get repeated values n times corresponding to distint number of the new attribute, I mean the new column. The total number of records (observations or rows) increases n times. Now we move into analysis/summarizing with pivot table or group by, the aggregation by the count for those variables with repeated values is incorrect. How do we fix or deal with this? Thank you.

[@shyamthakur9799]

In May 2020 update... can't find pivot or unpivot options in query editor.... Can you help me in that??

[@ezharezani5936]

Hi Ruth, can I sort the period columns into ascending order? which will show 12 months first

[@il_han8867]

Hi, you answered the questions I've been looking for for a long time. Thank you very much.

[@CurbalEN]

My pleasure :)

[@il_han8867]

I have a question. I'd appreciate it if you could answer that. How do I create an index column with a 0-1-0-1-0-1-0-1. Zero and one number will repeat.

[@CurbalEN]

It depends on how your data looks like. Post the question in the power bi community and give as many details as possible.

[@diegocalderon3221]

This was great! Ex2 is was super useful for turning my horizontal (5-min interval by day) into vertical interval by day. Thanks!

[@CurbalEN]

🥳

[@BISimplifier]

In Ex1 ,let's say we have millions of records and if we transpose that would be vertically align and wouldn't be an issue in PBI refreshing? I thought we should never use transpose if we having millions rows of tables.What you think about this?

[@3TUN_Official]

OMG this video was amazing!!!! TY SO MUCH!!! Literally saved me so much time

[@CurbalEN]

Wonderful to hear 👏👏👏

[@3TUN_Official]

@@CurbalEN Love all your videos! This is my go to channel for Power BI. TY for taking the time to help us with all your videos.

[@CurbalEN]

Knowing that it helps somebody is all I need to keep going, so thanks a million for sharing the feedback 😊

[@pandianselvaraj9204]

Thanks for u Yr explanation about transpose, pivot and pivot... For Q2 :i choose option 3( Microsoft azure HD insight )

[@EricMKO]

Just subscribed. Very useful content.

[@CurbalEN]

Welcome!

[@SalonValby]

Thank you so much for that help, Keep going WOW

[@andredeoliveira3684]

you're the best! thank you

[@elrevesyelderecho]

Hello. Thanks for mentioned me 😊😊, but I got the impression that I wasn't the first to answer correct the Q1. But, you have the stats so... Thanks. This one. I think is 4. Question said live data, so I was thinking first about SSAS datasource, but when you read the options # 4 it is the only that I know allows bring data for live server. Maybe I am wrong, but some of the other option sounds fake names. Thanks for this video. I was practicing that yesterday and now the question is related to embedded, topic that I have been reading about this week.

[@abhishekstatus_7]

Thanks for the explanation Ruth !! Really appreciated!!

[@CurbalEN]

My pleasure :) /Ruth

[@raulpinto1963]

Saved my day!!! thank you!

[@CurbalEN]

🥳🥳

[@rkburra]

Excellent Video

[@CurbalEN]

Thanks!

[@EricaDyson]

Well I got the first one right (only because I do it a lot), but as for Azure.. not a **** clue.. so look forward to your answer and explanation!

[@CurbalEN]

Well done! I will reveal it on Friday ;) /Ruth

[@godfreykiggundu7787]

Thanks alot my dear.

[@Mooney103]

Thank u so much for the video

[@YMQ9]

good video very helpful

[@Laxmanmane007]

thanks for clear explanation.

[@vishalgandhi4279]

Hi this video is superb but I stuck in my data can you please help me I have Country, Date, Power MW, Power Consumption, Energy MW, Energy consumption columns, Now I want output as Country, Date, MW (contain power and energy) in row, Mw Value, consumer value

[@Ernestofranco80]

love it. Thank you

[@HachiAdachi]

Answer = 4. My employer doesn't use any Azure data services so my experience and knowledge in this area are very limited. I look forward to the answer reveal and your explanations! (and I hope I maintain my perfect score! lol Do I get any credit for being the last one to answer Q1? LMAO) BTW, I had completely forgotten about the "Don't aggregate" option in Pivot Column (they shouldn't hide it 'Advanced Options', really...) After a few years spent with PBI, I'm still humbled by these little 'basic' things that I should've known... 😳 So thanks, Ruth! 😀

[@CurbalEN]

I am like you! I recently discovered that you can split columns to rows using the UI ...also hidden! No points for last sorry 😂😂😂 /Ruth

[@GainaIytics]

THANK YOU SO MUCH!

[@CurbalEN]

🥳🥳🥳

[@DHPDHPDHPDHP]

You are the Best I love yours videos!!!! Jújú

[@CurbalEN]

🥳🥳

[@manjunathshenoy2771]

Thank you.

[@walidkhlil2872]

Thanks again and again

[@timmytesla9655]

Thanks for this

[@CurbalEN]

Stay tuned for todays video, more on this!

[@nazirakhan6530]

Hi,Ruth Answer for Q2 is 4.

[@miltondiaz2531]

Perfecto. gracias

[@harithalluri8174]

dataset how to download

[@badreddinemachkour3643]

thanks a lot !!! very helpful !!

[@CurbalEN]

Wonderful to hear :) /Ruth

[@arulmanohar]

Great!

[@kalyanbabu6736]

Thank you mam

[@mikem2246]

Unfortunately, when I used a transpose, the column headers are disappeared 🤷‍♂

[@CurbalEN]

Yes, demote the column headers first!

[@mikem2246]

​@@CurbalEN I noticed that this is what is missing but I didn't find a way to do this (I would like to get a link if you have a video on this topic), so I transposed the table inside the excel file.

[@CurbalEN]

There is a button in power query where you can promote and demote headers, in the Home tab!

[@mikem2246]

@@CurbalEN 🧐

[@sandratorres1612]

Gracias !!!!

[@CurbalEN]

Un placer :)

[@ucheokeke4780]

Thanks for the content Ruth. I think the answer is 4 for this question. I have a question. is it possible to import multiple csv files from a folder when the schema is different in each of them? I want to do that using the Folder data source connection but it wants to combine the csv files. Thanks

[@CurbalEN]

Create a function for each csv, that should do it ;) /Ruth

[@dunder64]

Ruth, do you mean a function in Power Query?

[@vida1719]

Great comparison of transpose, pivot and unpivot - reminds Rubik's cube. I cannot answer the second question as we don't use Azure, even my BPI reports are published as PDF files :( I wish I could share them though Power BI Service one day

[@CurbalEN]

Oh that is a pity :( , hopefully they will change their minds soon! /Ruth

[@Rishi_CA]

1st! And great topic 👌🏼

[@CurbalEN]

You are ;) Do you have an answer for the quiz? /Ruth

[@tobibell2418]

Slightly convoluted.

[@kflynny23]

Great video as always. I thought this exam finished in June? And replaced by docs.microsoft.com/en-us/learn/certifications/exams/da-100? Very similar questions I assume, but just wanted classify as I'm keen to get booked into the new one

[@JJ_TheGreat]

0:30 That sounds very confusing. Your definitions make it sound like they are all exactly the same thing.

[@CurbalEN]

Did my best. Check other sources, they might resonate better for you.

[@nickdoy7019]

SQL as you would need the ability to query the source dataset rather than store the data in Power BI.

The correct answer is 4...you have to use SQL for live query

[@CurbalEN]

Thanks :) /Ruth

[@jjpk80]

Good entertainment :-D !!!

[@desenterrados2022]

I would say number 4...

[@Call_Me_VJ]

C1 C2 A 2 B 2 C 5 A 7 C 8 Output A B C 2 2 5 7 8 How to do this ?

[@flickers29]

Option 4: Microsoft Azure SQL Database

[@jhampiercito]

option 4

[@Abecedarianme]

Hi, I am beginner in powerbi and I am doing transformations of rows to columns but I am having difficulty in achieving it. The raw data (from table) looks something like this: YEAR REGION MEASURE_1 MEASURE_2 2020 ASIA 1 -43 2020 EUROPE 83 23 2020 N. AMERICA 37 32 2020 S. AMERICA -20 21 2019 ASIA 4 74 2019 EUROPE 3213 21 2019 N. AMERICA 32 65 2019 S. AMERICA 76 21 2019 AUSTRALIA 3 85 2018 EUROPE 11 22 2018 N. AMERICA 33 44 2018 S. AMERICA -55 66 2018 AFRICA 77 88 2018 AUSTRALIA 98 111 I want the output to be like this: 2020 2019 2018 REGION MEASURE_1 MEASURE_2 MEASURE_1 MEASURE_2 MEASURE_1 MEASURE_2 ASIA 1 -43 4 74 NULL NULL EUROPE 83 23 3213 21 11 22 N. AMERICA 37 32 32 65 33 44 S. AMERICA -20 21 76 21 -55 66 AUSTRALIA NULL NULL 3 85 98 111 AFRICA NULL NULL NULL NULL 77 88 ANTARTICA NULL NULL NULL NULL NULL NULL Hope you can help me with this.

[@juansepowerplatform]

hello i believe the answer is 3 for live data

[@CurbalEN]

Thanks ;)

[@Amine-p4o]

Transpose acts like pivot or unpivot but with no aggregation.

[@charugera7654]

question 2 - answer 4

[@CurbalEN]

Check the pinned comment to go to the solution :) /Ruth

[@salmanlone827]

Q2, My answer would be Microsoft Azure SQL Database

[@CurbalEN]

Thanks :)

[@bishwarai]

Number 4

[@elrevesyelderecho]

Ehh nope. I Google it and those are not name made up. So, I have doubts about HD Insight, but I am keeping my answer: #4 Azure SQL

[@CurbalEN]

They should offer Google on those exams....remembering that stuff is nuts!! We will see if 4 is right on Friday ;) /Ruth

[@blankpages3881]

Azure SQL Database

[@kim1217]

4

[@theperpetuallyannoyed4074]

Soooop confusing these 3 are... 🤯🤯 Still so confused

[@CurbalEN]

Oh no! :(

[@theperpetuallyannoyed4074]

@@CurbalEN yes 😭😭😭 still split by postion is unclear.... Need desperate help as m starting to learn bi😭 to pivot part is somewhat clear just need practice but split by position is so confusing

[@CurbalEN]

Don't worry, It took me quite some time too to get it right. What did it for me was practice. Continue practicing and eventually it will fall into place!

[@lmereu]

\o/

[@CurbalEN]

😂😂

[@vplin3322]

4