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!!
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!!!! 👍
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.
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
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?
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 ?
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.
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! 😀
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
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?
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.
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
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 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.
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
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.
@@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
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!
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!!
🎉
Thank you for the simple explanation. Sometimes, it can get confusing. But this video is a great reference!
This is exactly the video I needed to help me reconfigure some data I had in a rubbish format. Thank you so much!
Wonderful to hear!!
This is a very deep concept. Thanks for making it short and straight to the point
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!!!! 👍
🥳🥳🥳
/Ruth
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.
Thank you! Just the first examples you showed in the first minute answered my questions!!!
Excellent!
Didn´t know how to put into words what I was looking for: "Unpivot" is the answer. Thanks.
Yeah, the first hurdle is always to know hoe to search. Once you know that, the rest is easy ;)
Thank you, Ruth. This video solved a nagging issue a colleague was trying to soolve.
I understood just the definition. However, thanks @Curbal for igniting the interest in me.
Curbal, your content helped!! Thank You! Keep Growing!
Thank you for explaining things so clearly. This concept always used to confuse me.
Thank you for your explanation. I have a question, what if i just want to take one row and turn it to a column?
It was one of the most helpfull things I watched in youtube this year!
Thanks a lot!
Abraços do Brasil!
Delighted to hear :)
you save my life, my god this is very helpful thank you very much mrs
That exactly what I was looking. Thank you!!
Great explanation on 3 key tools in power query for shaping data! Thanks Ruth!
🥳🥳
thankyou for this. The best explanation i have seen.
Thanks for the great content :), for Q2 I believe the answer is number 4: Microsoft Azure SQL Database
Thanks Gustavo, I will reveal it on Friday :)
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.
Pleasure and glad it was helpful:)
Excellent. Thank you so much for clearly explaining with concise examples. You rock.
Love your content! Quick and concise
Thanks!
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
I use them all the time too :)
simple and clean thank you
At 4:13 when you removed Change Type, what did you click exactly?
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?
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 ?
1:33 So would it be possible to do the same thing WITHOUT transposing? If so, how would it be done? Thanks.
Thank you. This has helped me a great deal.
This was great! Ex2 is was super useful for turning my horizontal (5-min interval by day) into vertical interval by day. Thanks!
🥳
Thanks for u
Yr explanation about transpose, pivot and pivot...
For Q2 :i choose option 3( Microsoft azure HD insight )
Hi, you answered the questions I've been looking for for a long time. Thank you very much.
My pleasure :)
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.
It depends on how your data looks like. Post the question in the power bi community and give as many details as possible.
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! 😀
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
Hi Ruth, can I sort the period columns into ascending order? which will show 12 months first
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
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
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?
at the end how the last column sales come up ?? before it was not there right ?? how come 3 columns turned into 4 ?
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.
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!
Well done!
I will reveal it on Friday ;)
/Ruth
In May 2020 update... can't find pivot or unpivot options in query editor....
Can you help me in that??
Thanks for the explanation Ruth !! Really appreciated!!
My pleasure :)
/Ruth
Just subscribed. Very useful content.
Welcome!
Thanks alot my dear.
OMG this video was amazing!!!! TY SO MUCH!!! Literally saved me so much time
Wonderful to hear 👏👏👏
@@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.
Knowing that it helps somebody is all I need to keep going, so thanks a million for sharing the feedback 😊
thanks for clear explanation.
Hi,Ruth Answer for Q2 is 4.
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
Create a function for each csv, that should do it ;)
/Ruth
Ruth, do you mean a function in Power Query?
Excellent Video
Thanks!
I love you Curbal, u are the best:))))
You too!!
good video very helpful
Thank you so much for that help, Keep going WOW
Thank u so much for the video
THANK YOU SO MUCH!
🥳🥳🥳
Thank you.
you're the best! thank you
Saved my day!!! thank you!
🥳🥳
Thank you mam
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
Oh that is a pity :( , hopefully they will change their minds soon!
/Ruth
love it. Thank you
Thanks again and again
Perfecto. gracias
Thanks for this
Stay tuned for todays video, more on this!
Unfortunately, when I used a transpose, the column headers are disappeared 🤷♂
Yes, demote the column headers first!
@@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.
There is a button in power query where you can promote and demote headers, in the Home tab!
@@CurbalEN 🧐
You are the Best I love yours videos!!!! Jújú
🥳🥳
dataset how to download
Gracias !!!!
Un placer :)
thanks a lot !!! very helpful !!
Wonderful to hear :)
/Ruth
Great!
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
I would say number 4...
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 ?
Good entertainment :-D !!!
SQL as you would need the ability to query the source dataset rather than store the data in Power BI.
hello i believe the answer is 3 for live data
Thanks ;)
The correct answer is 4...you have to use SQL for live query
Thanks :)
/Ruth
option 4
Option 4: Microsoft Azure SQL Database
0:30 That sounds very confusing. Your definitions make it sound like they are all exactly the same thing.
Did my best. Check other sources, they might resonate better for you.
question 2 - answer 4
Check the pinned comment to go to the solution :)
/Ruth
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.
1st! And great topic 👌🏼
You are ;)
Do you have an answer for the quiz?
/Ruth
Q2, My answer would be Microsoft Azure SQL Database
Thanks :)
Number 4
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
They should offer Google on those exams....remembering that stuff is nuts!! We will see if 4 is right on Friday ;)
/Ruth
Slightly convoluted.
Transpose acts like pivot or unpivot but with no aggregation.
Azure SQL Database
4
\o/
😂😂
Soooop confusing these 3 are... 🤯🤯 Still so confused
Oh no! :(
@@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
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!
4