Power Query Unpivot - fix 4 common data layouts (incl. workbook) - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Our
Great video! Very informative. Only, it didn't help me figure out my issue, and I'm hoping you can provide a bit more expertise. I'm working on a survey, so a lot of the data collected is either 1 (for yes) or 0 (for no). I'm trying to create a pivot table that captures the total number and percentage of no's and yeses by age.
Within the pivot table builder, I've placed the question asked under Values, count under Values, and the age under Rows. This allows me to see the total yeses (1) per question by age but I cannot figure out how to also see the percent of yeses for each question.
When I move the question asked from the Values field, I can no longer see the question asked within the table - I'm only seeing either 1 or 0. I'm not great at Excel, but I am trying and hopeful you can provide some guidance. Thanks!
reply
Great video! Very informative. Only, it didn't help me figure out my issue, and I'm hoping you can provide a bit more expertise. I'm working on a survey, so a lot of the data collected is either 1 (for yes) or 0 (for no). I'm trying to create a pivot table that captures the total number and percentage of no's and yeses by age.
Within the pivot table builder, I've placed the question asked under Values, count under Values, and the age under Rows. This allows me to see the total yeses (1) per question by age but I cannot figure out how to also see the percent of yeses for each question.
When I move the question asked from the Values field, I can no longer see the question asked within the table - I'm only seeing either 1 or 0. I'm not great at Excel, but I am trying and hopeful you can provide some guidance. Thanks!
reply
Michael
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!!!
reply
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!!!
reply
Andrew
Hi Myanda, great tutorial, thank you! Question though, I am trying to query MYOB reports that do not have uniform repeating rows but, similar to the 4th example, I need the header row to the block of rows in its own comlumn for each row of data. For example, I download the Activity Slip [Employee Detail] for a period and then need to extract how much time has been spent on various jobs over that period. Presently, I have a series of helper columns to clean up MYOB's mess; not sure if there are easier ways of managing that, thanks
reply
Hi Myanda, great tutorial, thank you! Question though, I am trying to query MYOB reports that do not have uniform repeating rows but, similar to the 4th example, I need the header row to the block of rows in its own comlumn for each row of data. For example, I download the Activity Slip [Employee Detail] for a period and then need to extract how much time has been spent on various jobs over that period. Presently, I have a series of helper columns to clean up MYOB's mess; not sure if there are easier ways of managing that, thanks
reply
chilaw2004
The last data layout fix was really useful, but what if your data does not always have the same number of entries for each person? For example, what would you do if most people had just 3 rows of data--name, DOB, and hourly rate--but some people had a fourth (e.g., -on medical leave-)? If you had a table of 40 people and the 20th person was the only one with four entries, how could you avoid copying their status to everyone above them in the list if you -filled up-?
reply
The last data layout fix was really useful, but what if your data does not always have the same number of entries for each person? For example, what would you do if most people had just 3 rows of data--name, DOB, and hourly rate--but some people had a fourth (e.g., -on medical leave-)? If you had a table of 40 people and the 20th person was the only one with four entries, how could you avoid copying their status to everyone above them in the list if you -filled up-?
reply
Richard
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.
reply
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.
reply
Steve
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.
reply
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.
reply
Dianne
Hi Mynda, this is really helpful. Do you have any videos on unpivoting financial data? I'm getting data out of our budgeting system via Excel then unpivoting so that I have a single values column. What I can't figure out then is .how to calculate my variances from actual/forecast to budget or to prior year. Should I create a measure to do this? I currently don't have access to get the data straight from the database.
reply
Hi Mynda, this is really helpful. Do you have any videos on unpivoting financial data? I'm getting data out of our budgeting system via Excel then unpivoting so that I have a single values column. What I can't figure out then is .how to calculate my variances from actual/forecast to budget or to prior year. Should I create a measure to do this? I currently don't have access to get the data straight from the database.
reply
liew
Hi Treacy, thanks for the video, i would like to know if my column has text and number value, and i need them both in the same column, lets say i need number value to be in percentage and text as something else, is there away to make it stick in the same column? or you already have made a video about this problem, thanks, hope you can assist me on this, appreciate it.
reply
Hi Treacy, thanks for the video, i would like to know if my column has text and number value, and i need them both in the same column, lets say i need number value to be in percentage and text as something else, is there away to make it stick in the same column? or you already have made a video about this problem, thanks, hope you can assist me on this, appreciate it.
reply
Rawshan
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.
reply
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.
reply
Keith
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!
reply
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!
reply
Add a review, comment
Other channel videos
