Thanks Power Query Unpivot - fix 4 common data layouts 2017 06 05 ua-cam.com/video/-IMqkg35adA/v-deo.html 19min23 00:00 intro 00:40 - Ideal format, Tabular 00:57 - Step by step instructions in downloaded example file 01:05 - Sales info by person by year with row totals 01:40 - How to start PowerQuery 02:00 - PowerQuery Editor Window 02:50 - Unpivot the year columns 03:49 - Rename Columns 04:05 - Change data types 04:25 - Rename Query 04:32 - Close and Load 04:48 - Refresh query output table 06:13 - Example 2: Partial tabular, repeating columns monthly hours and costs 06:42 - load to PowerQuery 07:00 - Unpivot the “values” columns 07:28 - Split month out to separate column: Split Column feature 08:02 - Pivot the Attribut column to separate Hours and Costs 08:39 - Close and Load to Excel 09:13 - Example 3: column headings over 2 rows 09:45 - Format data as table 10:18 - Load to PowerQuery 10:21 - Merge 2 header rows into 1, Transpose data 10:43 - Merge the first columns 10:50 - Fill down month values 11:04 - Perform merge: Transform tab > Text Columns group > Merge Columns command 12:05 - Trim leading spaces 12:10 - transpose back 12:14 - Promote first row to become header 12:27 - Unpivot 12:48 - Split month out of attribute column 12:57 - Trim unwanted space at front of column/ 13:03 - Pivot the attribute column to split Account hours and account costs 13:16 - Rename columns 13:20 - Close and Load 13:50 - Example 4: Stacks of repeating rows 14:25 - Convert data to table 14:38 - Load to PowerQuery 14:44 - Filter to delete blank rows and total rows 15:14 - Add index column for unique numbers 15:33 - Pivot Column don’t aggregate 15:56 - Values now in staggered stacks with null values between rows 16:19 - Fill up: Transform tab > Table group > Fill up button 16:41 - review result, filled rows 17:00 - Delete duplicate rows with null values 17:09 - Remove Index column 17:16 - format date column as date only 17:44 - close and load . Keep up the good work.
Thank you Mynda! I've been following your videos for a while. You have the best ones dedicated to Excel. The fact that you share your files and resources is also a blessing 🙏 Keep it up 🙂
Thanks for another extremely informative and practical Excel mastery video. I started teaching myself Power Query only a few months ago, after over 27 years as an Excel developer. I can honestly say that there's no turning back. Power Query will forever change how I interact with my favorite spreadsheet program. Thank you for explaining each of these common scenarios in concise and clear terms. Everything makes sense as you presented it. I think for me, the main challenge is to develop the insight and intuition to know which Power Query menu options to use and in what sequence. It is not always immediately obvious to me when to pivot/unpivot/transpose - sometimes repeatedly - to rearrange the data into a final tabular format ready for analysis. I've done dozens of Power Query exercises on datasets just like the ones you covered in the lecture. I guess I will get better at it with continued practice and patience. Sometimes I end up writing complex but effective custom reusable M Code functions to arrive at the desired result, only to find out later that it could have been accomplished strictly through the UI! 🙂 Thank you!
Incredible! I've been training PowerPivot, PowerQuery, PivotTables for a very long time and didn't know there were such amazing tools to fix these data issues! Wow! Thank you so much!
Two thumbs up from me. I've been working with Excel since the start, and did in fact have to hand massage lots of data sets manually or with the complex formulae you mention. If Power Query had been available then I would have saved months of work.
I work as a Power BI developer and I'm a fresher. Today I encountered a similar problem at my work and BAM! You're video just saved me so many hours of research! Thank you so much
Mynda, thank you for this video! You've changed my life. Over this weekend, I had to pull data from over 400 bi-weekly print-formatted timecards made in excel. They each had 2 non-formatted tables of weeks, and separate rows for each department worked in (thankfully, column & row numbers were consistent for all of them). Using a combination of your pivot scenarios plus your "multiple files containing multiple sheets" video, I was able to get the data from ALL of the timecards in tabular format (one row per dept-date-hours) in just about 8 hours' work! And I can just refresh the query as new timecards are added! Not to mention all the error-catching utility. Thank you!!!
Thank you so much. I've recently started an online business that involves Microsoft Excel Power Query and your video was definitely an inspiration for that. Awesome teacher. I'd recommend you any day.
Thank you Ma'am. I try to watch all your videos. I have learned power query, dashboard, advance pivot, pivot chart from your video. It helped me a lot to do my work properly and more efficiently and it makes my life easy and shortens my report making procedures.
I am an excel enthusiast and was soooo excited watching this video...I absolutely loved it... learned something so complex in such understanding and well explained tutorial... Thank you so much...
Brilliant! The more of your videos I watched the more impressed I am with Power Query's capabilities, to say nothing of the easy-to-follow and effective teaching methods you employ. Great work, and thank you! I am making a mental note of the work I can save, having previously used a witches' brew of clunky formulae to harvest data that I have to re-familiarize with each time I do the occasional work. Pity I can only upvote you once on this.
@@MyOnlineTrainingHub It's been a great learning experience for the past week. I joined your site and posted a question there, and I'm hoping to get some guidance on my little project (where PQ reduced my data collection/massaging time from 5 minutes - with risk of errors - to 18 SECONDS, error-free). Ciao, Mynda!
First of all, it’s incredible how it looks like every problem has a solution with this method, how someone thought of all the options. Second, you’re a godsent, Miss, you’re my favorite Excel and data youtuber. I just sent a comment in another video about me having a problem with a very messy table and YT recommended me this video (I haven’t had seen yet) in my TL. This made me realize I have to keep practicing, there’s a lot to learn!
Of all your very helpful videos, this one will actually save me the most time, and help me the most. It gives solutions to problems I've occasionally had over the years, but have lately been confronted with over and over again. Thank you!
Thank you Mynda! You have done a fantastic job to explain the concepts of power query, and this makes a big difference in my life. Your deep knowledge of excel, tone of voice and speed in the demonstrations are just perfectly blended in the video and it is entertaining to watch your UA-cam channel. Two thumbs up!
Thanks, few days ago I was in search of transforming data as your last example but failed. I have asked the way out from a well known forum but no response was given to me till today. However I have made it by the help of PQ and formulas in excel. If I came to this video then I could safe me from a lot of hard working. I have learned a lot from your tutorials. Thanks again.
You are awesome, thank you. The repeating data issue has been driving me mad for weeks. I love the fill up method idk why I never thought of it as I’ve been using fill down.
Thanks Mynda, I've already seen many power query preparation, never check how to start to prepare my data and the video was 2017! It's helpful to see that there's an start, thanks for help, be blessed.
Glad I came across your examples. It gave me an idea how to deal with some messy tabulated data similar to the second example however, my dilemma is that instead of just numbers, the values below the columns include text strings. For example, column 1 is the unique audit id, column 2 is the score for parameter 1, column 3 is the comment for parameter 1, column 4 is the score for parameter 2, column 5 is the comment for parameter 2, and so on and so forth. I have 7 parameters in total. I need the data to be in rows in a way that I have all the scores in 1 column but still with the comments beside them. I'll try to experiment using the steps you shared and hopefully be able to figure it out.
Glad you're giving Power Query a try. It's difficult to help from a description. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Brilliant video, some of the data we get from third parties is something I'd be ashamed to send out myself! This is the perfect antidote to fix it. Thank you!
Very very very helpful lesson for training my power query skills every weekend - hopefully I can use it at my work between the weeks :-). Also very pleasant that the file is available, thank you so much for that !!! 😊
I know that this video is 6 years old now but you have saved me a great deal of stress and grey hair, thank you for making this wonderful and informative video.
Wow! I have been a subscriber for quite a while, and this is the first time I have seen this video. I have been struggling with trying to use Power Query to get Quicken reports in a use able format to use in Pivot Tables. I will use some of the techniques from this video to hopefully finally have success. Thank you! I find all your videos excellent.
Thank you! Hopefully, Power Query can sort out your Quicken reports. If you get stuck, feel free to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I like what you done, by simplifying it to enable people like me to understand it in more depth. i knew this feature some how lost it object and ways. thanks for such beautiful videos. Just one more thing your voice makes it pleasing to hear. :) continue your good work !! more power to you...
Great video. It really really is. I do have one issue with it though. In the "real world"(!), people who send unstructured data invariably make subtle changes to the format each month (assuming monthly). An easy example would be your third, where the blank rows are all a uniform pattern. I sighed when I saw this bit as it doesn't reflect painful reality. And this is an easy example. This goes for many of your videos. I'd love to see some more complex examples where you try to straighten out data that has variable formats. Thanks again, absolutely loving the videos and have learned heaps.
Thanks, Martin! All of my videos are based on questions I get asked from real people. I agree, there are sometimes more complex problems and for that we offer support via our forum where you can upload a sample file that illustrates your scenario: www.myonlinetraininghub.com/excel-forum
Had a really poorly organized data set and watched this maybe ten times while walking through my data and finally it all clicked! With the tranpose/ pivot/ unpivot it is like an excel based Rubik's Cube! The only challenge I have is I have a column with the "YEAR" in YYYY format. When I change from Whole Number to Date it gives me mm/dd/yyyy that is not even close. Not sure what is going on but major victory to get all the data organized to do a better dashboard.
Wow, awesome to hear, Richard! If you have a column that only contains the year e.g. 2022, how do you want Power Query or Excel to determine the day and month components? You'd need to add a custom column using a function like =#date([year],12,30) to return 12/30/2022
@@MyOnlineTrainingHub If it needs to show the full mm/dd/yyyy, showing the last day of the month (when the data is pulled) would be ideal. I will give this a try. Just "2019" alone was converting to 7/1/1905. Again, really odd result.
Great video! I currently have to work with documents that are formatted where there are repeated tables separated by blank row and the next tables "category". The category text is entered in the same column as each item description. I want to unpivot that so that there is a row with the category for each of the items listed in that table. It's similar but too different from any of these examples. It's killing me to manually add a new column, copy and paste that category text next to all of the items in that subtable. I don't know if I'm doing a great job explaining it. I do love that you went through these common scenarios! Thanks so much for the share.
You can add a custom column that checks for a blank cell in one of the header rows, if it's blank it brings in the category name and then copies it down. You can then filter out the blanks and header rows. If you're still stuck, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
What an excellent tutorial, Mynda, it's a pleasure to watch your lessons, you have the gift to explain things in a fun and interactive way. Thank you very much!!
Thanks
Power Query Unpivot - fix 4 common data layouts 2017 06 05
ua-cam.com/video/-IMqkg35adA/v-deo.html 19min23
00:00 intro
00:40 - Ideal format, Tabular
00:57 - Step by step instructions in downloaded example file
01:05 - Sales info by person by year with row totals
01:40 - How to start PowerQuery
02:00 - PowerQuery Editor Window
02:50 - Unpivot the year columns
03:49 - Rename Columns
04:05 - Change data types
04:25 - Rename Query
04:32 - Close and Load
04:48 - Refresh query output table
06:13 - Example 2: Partial tabular, repeating columns monthly hours and costs
06:42 - load to PowerQuery
07:00 - Unpivot the “values” columns
07:28 - Split month out to separate column: Split Column feature
08:02 - Pivot the Attribut column to separate Hours and Costs
08:39 - Close and Load to Excel
09:13 - Example 3: column headings over 2 rows
09:45 - Format data as table
10:18 - Load to PowerQuery
10:21 - Merge 2 header rows into 1, Transpose data
10:43 - Merge the first columns
10:50 - Fill down month values
11:04 - Perform merge: Transform tab > Text Columns group > Merge Columns command
12:05 - Trim leading spaces
12:10 - transpose back
12:14 - Promote first row to become header
12:27 - Unpivot
12:48 - Split month out of attribute column
12:57 - Trim unwanted space at front of column/
13:03 - Pivot the attribute column to split Account hours and account costs
13:16 - Rename columns
13:20 - Close and Load
13:50 - Example 4: Stacks of repeating rows
14:25 - Convert data to table
14:38 - Load to PowerQuery
14:44 - Filter to delete blank rows and total rows
15:14 - Add index column for unique numbers
15:33 - Pivot Column don’t aggregate
15:56 - Values now in staggered stacks with null values between rows
16:19 - Fill up: Transform tab > Table group > Fill up button
16:41 - review result, filled rows
17:00 - Delete duplicate rows with null values
17:09 - Remove Index column
17:16 - format date column as date only
17:44 - close and load
.
Keep up the good work.
Thanks so much for sharing, Ross! Perhaps I should give you a job :-)
Hhhvohhhh. Hhhohv
Hhhvohhhh. Hhhohvv
.
This video almost made me cry. This is going to be a massive time saver.
Wow, so pleased to hear that, Jason!
😢 just transposed some data yesterday from 20 columns into 400 rows. Did it now within 10mins! Thanks!
I watched this video 5 years ago and today it helped me with a task. These videos never lose their relevance. Thank you Mynda
Wow, that's awesome to hear!
same.
this just blew my mind. I've solved so many of these problems by manually copy and pasting data over the years! 🤯
No more copying and pasting now then, Jared :-) Glad it'll save you some time.
This was the biggest amounts of "AHA's" I've said in ages! Amazing tutorial!
Awesome to hear, Flavio :-)
Dear Mynda, You must be awarded the Accounting Data Cleaning Nobel Prize for this video at the very least! Thanks! :-)
😁thanks so much!
Madam, you are an excellent teacher!
Thank you Mynda! I've been following your videos for a while. You have the best ones dedicated to Excel. The fact that you share your files and resources is also a blessing 🙏 Keep it up 🙂
Wow, thank you!
Thanks for another extremely informative and practical Excel mastery video. I started teaching myself Power Query only a few months ago, after over 27 years as an Excel developer. I can honestly say that there's no turning back. Power Query will forever change how I interact with my favorite spreadsheet program.
Thank you for explaining each of these common scenarios in concise and clear terms. Everything makes sense as you presented it. I think for me, the main challenge is to develop the insight and intuition to know which Power Query menu options to use and in what sequence.
It is not always immediately obvious to me when to pivot/unpivot/transpose - sometimes repeatedly - to rearrange the data into a final tabular format ready for analysis. I've done dozens of Power Query exercises on datasets just like the ones you covered in the lecture. I guess I will get better at it with continued practice and patience.
Sometimes I end up writing complex but effective custom reusable M Code functions to arrive at the desired result, only to find out later that it could have been accomplished strictly through the UI! 🙂
Thank you!
Great to hear! I know what you mean about knowing which tools to use in what order is not always obvious, but in time you'll become adept at it. 😊
These are perfect examples to solve many of the every day's issues when managing data models!!!
Thank you Mynda!
You're very welcome!
Thank you a lot. This is going to speed up my work for sure. I really didn't know there was such an option like unpivot! My jaws literally dropped!
:-) great to hear!
Thanks for this... I was stuck for about a month in trying to solve the reapeted rows thing. your solution is AWESOME!
Glad it helped!
Incredible! I've been training PowerPivot, PowerQuery, PivotTables for a very long time and didn't know there were such amazing tools to fix these data issues! Wow! Thank you so much!
Glad to help! 😊
One of the best PQ videos I've seen on here and easy to follow. THANK YOU!
Absolutely no expression....on my face.
Well explained such a great video and contribution to the world of excel users.
Two thumbs up from me. I've been working with Excel since the start, and did in fact have to hand massage lots of data sets manually or with the complex formulae you mention. If Power Query had been available then I would have saved months of work.
Yeah, I hear you 😔
this video really help. I work with Excel everyday and am slowly transitioning to power query to make things more automatic. thank you !!
Great to hear, Maggie!
I work as a Power BI developer and I'm a fresher. Today I encountered a similar problem at my work and BAM! You're video just saved me so many hours of research! Thank you so much
Great to hear 😊
Mynda, thank you for this video! You've changed my life.
Over this weekend, I had to pull data from over 400 bi-weekly print-formatted timecards made in excel. They each had 2 non-formatted tables of weeks, and separate rows for each department worked in (thankfully, column & row numbers were consistent for all of them). Using a combination of your pivot scenarios plus your "multiple files containing multiple sheets" video, I was able to get the data from ALL of the timecards in tabular format (one row per dept-date-hours) in just about 8 hours' work! And I can just refresh the query as new timecards are added! Not to mention all the error-catching utility.
Thank you!!!
So pleased to hear that, Michael!
Y
We
Wow ❤ It is amazing how you have shared such valuable knowledge with everyone. Thank you so much. The internet thrives because of people like you.
So nice of you!
Thank you so much. I've recently started an online business that involves Microsoft Excel Power Query and your video was definitely an inspiration for that. Awesome teacher. I'd recommend you any day.
Thanks for your kind words, Jamie! Good luck with your new business :-)
Thank you Ma'am. I try to watch all your videos. I have learned power query, dashboard, advance pivot, pivot chart from your video. It helped me a lot to do my work properly and more efficiently and it makes my life easy and shortens my report making procedures.
Great to hear, Rawshan!
I am an excel enthusiast and was soooo excited watching this video...I absolutely loved it... learned something so complex in such understanding and well explained tutorial... Thank you so much...
Thank you! Glad I could help. Please share this with your colleagues. We need to spread the word about how amazing Power Query is.
This series is incredibly helpful for me. I always shied away from tables and pivot tables, but now I have the poweeeeerrrr! 💕
:-) great to hear!
Brilliant! The more of your videos I watched the more impressed I am with Power Query's capabilities, to say nothing of the easy-to-follow and effective teaching methods you employ. Great work, and thank you! I am making a mental note of the work I can save, having previously used a witches' brew of clunky formulae to harvest data that I have to re-familiarize with each time I do the occasional work.
Pity I can only upvote you once on this.
Thanks for your kind words, Steve! Great to hear you will have lots of uses for Power Query 😊
@@MyOnlineTrainingHub It's been a great learning experience for the past week. I joined your site and posted a question there, and I'm hoping to get some guidance on my little project (where PQ reduced my data collection/massaging time from 5 minutes - with risk of errors - to 18 SECONDS, error-free). Ciao, Mynda!
after 4years and a half ... this video of yours is still fresh and absolutely useful
Thank you
So pleased to hear that!
Thank you for this! I'm literally screaming with joy over being able to get the nested columns done.
Awesome to hear!
First of all, it’s incredible how it looks like every problem has a solution with this method, how someone thought of all the options. Second, you’re a godsent, Miss, you’re my favorite Excel and data youtuber. I just sent a comment in another video about me having a problem with a very messy table and YT recommended me this video (I haven’t had seen yet) in my TL. This made me realize I have to keep practicing, there’s a lot to learn!
Thanks for your kind words!
Great teaching. So clear, practical, and relevant to data issues I run into at work all the time. Thank you!
Glad it was helpful, Aaron!
The BEST example to keep as reference! VERY professional and easy to follow. THANK YOU !!
Glad it was helpful!
I searched the last scenario for days and you finally show me how to do it. Thank you!
Glad it was helpful 😊
Of all your very helpful videos, this one will actually save me the most time, and help me the most. It gives solutions to problems I've occasionally had over the years, but have lately been confronted with over and over again. Thank you!
Great to hear!
Thank you Mynda! You have done a fantastic job to explain the concepts of power query, and this makes a big difference in my life. Your deep knowledge of excel, tone of voice and speed in the demonstrations are just perfectly blended in the video and it is entertaining to watch your UA-cam channel. Two thumbs up!
Wow! Thanks so much for your kind words 🙏
This was excellent! I always learn SO MUCH from you so THANK YOU again!
You are most welcome, Jen!
Thanks, few days ago I was in search of transforming data as your last example but failed. I have asked the way out from a well known forum but no response was given to me till today. However I have made it by the help of PQ and formulas in excel. If I came to this video then I could safe me from a lot of hard working. I have learned a lot from your tutorials. Thanks again.
Glad it helped! Better late than never 😊
You are awesome, thank you. The repeating data issue has been driving me mad for weeks. I love the fill up method idk why I never thought of it as I’ve been using fill down.
Glad it helped!
You're extraordinary on handling data with Excel! I love your videos and thank you infinitely!
Thank you! I'm glad you found the video helpful :-)
Thanks Mynda, I've already seen many power query preparation, never check how to start to prepare my data and the video was 2017! It's helpful to see that there's an start, thanks for help, be blessed.
Thanks, Wess! Glad I could help.
This video saved my life 2 times, in different periods, in different cases. THANK YOU!
Wow, that's awesome to hear, Paulo :-)
You don't know Dear,
how much you save my times?
Thank You soo much.
Wonderful to hear!
The last example was especially cool to watch! Thank-you!
Great to hear you enjoyed it, Calvin :-)
Fantastic explanation with different scenarios and an absolute time saver for many,
Glad it was helpful!
you have beautifully explained the most common faulty format cleansing. you are better than Microsoft professional trainers.
Thanks for putting these together. Love your videos, they are simple and straight forward
Thank you Mynda! The second example was exactly what I was looking for. Works perferctly! Cheers!
Glad it was helpful!
Truly a masterful video. Thanks for all that you do.
So glad it was helpful 🙏😊
Thanks for this video it helped me resolve a very complicated problem for one of my client. Love your videos keep them coming.
This video explain most common format problems. It is great and straight to the point. Thank you!
Great to know it was helpful!
Didactic, clear and calmly explained. Excellent video!
Thank you kindly, Galina!
Wow - the download file is awesome! Best I have ever seen / just went through all the examples with 100% success rate!
Awesome to hear, Greg 🙏
Watched multiple videos on how to do this. By far the best video, great pace to the lesson and clear instructions. Thanks for the great work
Thank you! Great to know you found it helpful :-)
Glad I came across your examples. It gave me an idea how to deal with some messy tabulated data similar to the second example however, my dilemma is that instead of just numbers, the values below the columns include text strings. For example, column 1 is the unique audit id, column 2 is the score for parameter 1, column 3 is the comment for parameter 1, column 4 is the score for parameter 2, column 5 is the comment for parameter 2, and so on and so forth. I have 7 parameters in total. I need the data to be in rows in a way that I have all the scores in 1 column but still with the comments beside them. I'll try to experiment using the steps you shared and hopefully be able to figure it out.
Glad you're giving Power Query a try. It's difficult to help from a description. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Wow, I'm so glad I found this video, it's clear, concise, and effective as hell, great job and format... definitely keeping this one!!!!😀
Awesome, thank you!
Thanks, Mynda! You are so good. I will have to watch it a couple of times to totally understand.
Glad it was helpful!
Brilliant video, some of the data we get from third parties is something I'd be ashamed to send out myself! This is the perfect antidote to fix it. Thank you!
Glad you can make use of it 😊
The best and easy to understand video on this topic! thank you so much
Great to hear 🙏
Great... Cover the difficulties as an example is great.
Glad you found it useful, Sonu :-)
grateful Mynda for these amazing excel tips. you are kind
Glad you like them!
Excellent content and teaching!! Congratulations and thank you very much for sharing this knowledge.
Glad it was helpful! 🙏😊
The best tutor I have seen
Thanks so much!
You saved my day! I have been thinking how to do case #2 and Voila!..you had it here so well explained. THANKS!!!!
Glad I could help, Xiomara!
Very very very helpful lesson for training my power query skills every weekend - hopefully I can use it at my work between the weeks :-). Also very pleasant that the file is available, thank you so much for that !!! 😊
Awesome! Have fun with Power Query!
The way you present is awesome thanks of lots 😊
Thank you 😊
Thanks a lot Mynda. This is a great video, not only techniques but also supporting documents. I love it so much.
Thank you, Nattawut :-)
I know that this video is 6 years old now but you have saved me a great deal of stress and grey hair, thank you for making this wonderful and informative video.
You are most welcome! 😊
This is the most practical and excellent tutorial I really need. Thank you Madam
Wow, so pleased you found it helpful :-)
WHAT A GREAT YOU ARE ALSO PROVIDING EXAMPLE WORKBOOK.
Yes, the link is in the video description.
Greatest video ever not even gonna lie , this will save me hours 😂😂😂
Wow, awesome to hear!
Excellent video on Unpivot! Thank you for sharing the video!!
Glad you enjoyed it!
Thank you! It's an amazing, constructive with a good pace video! It helps to solve my work tasks perfectly.
Glad it helped, Vasilisa :-)
This is GREAT! Solving the most common situations
Glad I could help :-)
Wow! I have been a subscriber for quite a while, and this is the first time I have seen this video. I have been struggling with trying to use Power Query to get Quicken reports in a use able format to use in Pivot Tables. I will use some of the techniques from this video to hopefully finally have success. Thank you! I find all your videos excellent.
Thank you! Hopefully, Power Query can sort out your Quicken reports. If you get stuck, feel free to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you for the reply and suggestion. I will definitely do that.
I'm a real fan!
thank you so much. from your presentation I have found solutions from my bottleneck facing in day to day work
Great to hear I could help!
Superb Video Madam, One of the best vidoe i have ever seen on Unpivot. Thank you viery much. I can related two sheets related to my work.
So pleased you found it useful, Bharat :-)
This was exactly what I needed to see. Thank you so much for putting this together!
I like what you done, by simplifying it to enable people like me to understand it in more depth. i knew this feature some how lost it object and ways. thanks for such beautiful videos.
Just one more thing your voice makes it pleasing to hear. :)
continue your good work !! more power to you...
Glad it was helpful, Sachin!
Great video. It really really is.
I do have one issue with it though. In the "real world"(!), people who send unstructured data invariably make subtle changes to the format each month (assuming monthly).
An easy example would be your third, where the blank rows are all a uniform pattern. I sighed when I saw this bit as it doesn't reflect painful reality. And this is an easy example.
This goes for many of your videos. I'd love to see some more complex examples where you try to straighten out data that has variable formats.
Thanks again, absolutely loving the videos and have learned heaps.
Thanks, Martin! All of my videos are based on questions I get asked from real people. I agree, there are sometimes more complex problems and for that we offer support via our forum where you can upload a sample file that illustrates your scenario: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thanks :-)
Had a really poorly organized data set and watched this maybe ten times while walking through my data and finally it all clicked! With the tranpose/ pivot/ unpivot it is like an excel based Rubik's Cube! The only challenge I have is I have a column with the "YEAR" in YYYY format. When I change from Whole Number to Date it gives me mm/dd/yyyy that is not even close. Not sure what is going on but major victory to get all the data organized to do a better dashboard.
Wow, awesome to hear, Richard! If you have a column that only contains the year e.g. 2022, how do you want Power Query or Excel to determine the day and month components? You'd need to add a custom column using a function like =#date([year],12,30) to return 12/30/2022
@@MyOnlineTrainingHub If it needs to show the full mm/dd/yyyy, showing the last day of the month (when the data is pulled) would be ideal. I will give this a try. Just "2019" alone was converting to 7/1/1905. Again, really odd result.
I wish I found thıs video before I spent 2 weeks in my budget tables. :) thank you for sharing knowledge
Hopefully you can use it next time 😁
@@MyOnlineTrainingHub for sure! :) I am excited to apply in future books thanks again!
More video like this please. So easy to understand. You are great. Thank you so much.
Thank you! Glad you found it useful :-)
Brilliant and well presented. Thank you, Mynda.
Glad you enjoyed it! 🙏😊
Thank you for your video. You clearly explained a complex subject in easy to follow steps.
Thanks, Leonardo. Great to know you found it easy to follow.
Great video! I currently have to work with documents that are formatted where there are repeated tables separated by blank row and the next tables "category". The category text is entered in the same column as each item description. I want to unpivot that so that there is a row with the category for each of the items listed in that table. It's similar but too different from any of these examples. It's killing me to manually add a new column, copy and paste that category text next to all of the items in that subtable. I don't know if I'm doing a great job explaining it. I do love that you went through these common scenarios! Thanks so much for the share.
You can add a custom column that checks for a blank cell in one of the header rows, if it's blank it brings in the category name and then copies it down. You can then filter out the blanks and header rows. If you're still stuck, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great Video. I find it hard to get my head around Pivoting/Unpivoting data. Thanks for providing the file. I will practice more.
Glad you liked it 😊
thanks for sharing this. you are heaven sent.
You are so welcome 🤗
Thank you so much!!!! This video (and all your videos) are amazing!!!
Thanks so much!
Wow! Thanks for the thorough tutorial on the unpivot function.
:-) you' re most welcome, Joan. Glad you found it helpful. Please spread the word about how great Power Query is.
Very good and concise explanation of different ways to do the UnPivot function.
Cheers, Eddy :-)
Thanks a lot Mynda! This has helped me a lot. 🎉
I'm so glad!
This is absolutely superb! If I could like this video more than once I would!! Thank you Mynda!
:-) thank you! Great to know you liked it. Tell your colleagues how great Power Query is.
Nice content . Please always share your dataset so that we can practice along
Thank you. You can get the workbook here: www.myonlinetraininghub.com/power-query-unpivot-scenarios
MARVELOUS---MAM YOU INCREASED MY APPETITE---------------NEED MORE
Great to hear, Khalid!
Thanks Lynda for this very useful information for my most faced problems. Appreciate ur hardwork. I will soon jump into your course too..Thanks alot😍🙏
Glad it was helpful! Look forward to teaching you more Power Query wonders in my course 😊
Great solutions for scenarios specially the nested titles: they are the most difficult to deal with. Thanks
Good to know it was useful for you.
More than wonderful video. Thank you very much Mynda.
Thank you, Luby! :-)
Thank you, these are great tips! You explain this so well. 🤔
Thanks, Andrew!
I LOVE these videos!! Thank you so much
Great to hear! 😊
What an excellent tutorial, Mynda, it's a pleasure to watch your lessons, you have the gift to explain things in a fun and interactive way. Thank you very much!!
Aw, thanks for your kind words :-)
Super. Loved the way you explained all the scenarios. Thank you !
Thanks so much, Sachin!
Very clear and helpful walkthrough! Thank you very much.
Great to hear, Dwayne!