3 Methods to unpivot in Excel using Power Query

3 Methods to unpivot in Excel using Power Query | Excel Off The Grid

Рет қаралды 6,656

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post & download the example file★
exceloffthegrid.com/power-que...
★ About this video ★
I used to be amazed at people who could use complex formulas. I picked up lots of tips and tricks by learning these advanced techniques. But I soon discovered that getting data in the correct format at the start meant I didn’t need those advanced formula skills. Because once the data is in the proper structure, Excel becomes easy!
0:00 Introduction
1:05 Basic unpivot in Power Query - Method 1
3:42 Unpivot with multiple columns - Method 2
6:31 Unpivot with multiple header rows - Method 3
11:00 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PowerQuery

Пікірлер: 52

@findict2570 7 ай бұрын

The best I could have found for Unpivot.

@mohammadalbizri2013 5 ай бұрын

Highly appreciated.

@nuqwestr Жыл бұрын

For those curious. the photo in the background is Frank Sinatra in the "spotlight" taken by Phil Stern at the JFK Inaugural Ball Rehearsal, Washington DC, 1961.”

@ExcelOffTheGrid Жыл бұрын

That's really interesting. Obviously, I knew it was Frank Sinatra, but I had no idea where or when it was taken. There is just something about the lighting/shadows that I really like.

@khalidmajeed2886 Жыл бұрын

sir you did a great job.....................iiNFLUENCER

@ExcelOffTheGrid Жыл бұрын

Thank you 😀

@nitinsharma4121 4 ай бұрын

Thanks ❤

@ExcelOffTheGrid 4 ай бұрын

You're welcome 😊

@Back1Ply Жыл бұрын

10//10 Great Video !

@ExcelOffTheGrid Жыл бұрын

10/10 - i'll take that 😂

@FRANKWHITE1996 Жыл бұрын

thanks for sharing

@ExcelOffTheGrid Жыл бұрын

My pleasure - Thanks for watching Frank.

@Mohamed.GadAllah 3 ай бұрын

What a master! Thanks a lot :)

@ExcelOffTheGrid 3 ай бұрын

Thanks. Glad you liked it! 😁

@td99d Жыл бұрын

Great video! Love the trick with multiple headers. Thank you!

@ExcelOffTheGrid Жыл бұрын

Thanks Trevor, I’m glad you found it useful.

@shiffamohammed5818 Жыл бұрын

Thank you so much Mark, for sharing such an amazing power query video.

@ExcelOffTheGrid Жыл бұрын

You're welcome, I'm glad you found it useful.

@inaction2024 Жыл бұрын

Very clear and concise explanations. Thanks for showing us the techniques.

@ExcelOffTheGrid Жыл бұрын

Glad it was helpful! 😁

@SamehRSameh Жыл бұрын

Marvelous 3rd method

@ExcelOffTheGrid Жыл бұрын

Thank you! Cheers!

@ExcelWithChris Жыл бұрын

Thanks. Greetings from South Africa.

@ExcelOffTheGrid Жыл бұрын

You’re welcome.

@IvanCortinas_ES Жыл бұрын

Great solution the 3rd case.

@ExcelOffTheGrid Жыл бұрын

Thanks Ivan, I’m glad you found it useful.

@axeemx Жыл бұрын

Brilliantly explained... thanks Mark.

@ExcelOffTheGrid Жыл бұрын

Thanks Azeem - I'm glad you found it useful.

@sedarathnadcd Жыл бұрын

Thank you. It is a great help to understand complex data cleaning situations.

@ExcelOffTheGrid Жыл бұрын

Thank you, I'm glad it was helpful.

@gospelmoto2833 8 ай бұрын

Excellent! another good things is it has a sample file to practice with. Many Thanks! Got a new sub here.

@robertowerneck6902 Жыл бұрын

Great video. Thank you!

@ExcelOffTheGrid Жыл бұрын

Glad you liked it!

@IvanCortinas_ES Жыл бұрын

Superb video Mark. Thank you for these complete explanation.

@ExcelOffTheGrid Жыл бұрын

Thanks Ivan.

@daleanderson5258 Жыл бұрын

Great video. I know I can use the techniques you demoed. I'll just have to review the video a few more times. Thank you.

@ExcelOffTheGrid Жыл бұрын

Thanks 😀 If you download the example file from the blog post you can do a bit of practice before trying it on your own data.

@MrDhunpagla Жыл бұрын

Wonderful 😊 Thanks for sharing 🤗

@ExcelOffTheGrid Жыл бұрын

My pleasure 😊

@nassimbahloul3081 Жыл бұрын

Très pratique, Grand Merci.

@ExcelOffTheGrid Жыл бұрын

De rien. 😁

@nsanch0181 Жыл бұрын

Than you for the great video. I think I've seen ech one of those examples in real life so it was very helpful seeing you transform the with Power Query.

@ExcelOffTheGrid Жыл бұрын

Thank you. Power Query is the answer. 😀

@stocker.76 Жыл бұрын

Très intéressant Serais contente de suivre vos cours

@ExcelOffTheGrid Жыл бұрын

J'ai un programme de formation sur exceloffthegrid.com/academy, auquel vous êtes invités à vous joindre.

@arne.munther Жыл бұрын

Instead of click on the columns header to rename Attributes to Month, you can go to m-code and make the change there. It will give you one step less.

@ExcelOffTheGrid Жыл бұрын

Very true. I also would remove the auto applied steps. But I got to try and start where the viewer is likely to be.

@ExcelWithChris Жыл бұрын

What is the work around if you return data to a table back into Excel, but then want to do all sorts of calculations with it not being in a table? Do you copy and paste value to another sheet??? But then you will have to do it every time you update. The issue is if you return it as a table and then in the returned table add new columns with data. (for example now you add a new column for a region.

@ExcelOffTheGrid Жыл бұрын

I can’t visualise the situation you are describing. Never copy and paste, it loses all the benefit gained. If you add a new column to the Table you will need to update your formulas to account for the new categorisation anyway.

@ExcelWithChris Жыл бұрын

@@ExcelOffTheGrid I assume it would be best to add for ex a Region / Outlet / Country column in the original data. What if you want to use Subtotals with Data Subtotals option to the new table. Will you first have to remove subtotals, refresh and run Subtotals again?

@ExcelOffTheGrid Жыл бұрын

@@ExcelWithChris Tables hold data in a structured format. We then use formulas or PivotTables to calculate on the Table to create the presentational format. If you try to use a Table for presentation as I think you are suggesting, you are using the wrong tool for the job.