Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model


Paul Whisler

100 Comments

  1. First off. I bought courses(including Marco Russo) but nothing comes close to your teaching style. Hands down the best teacher on youtube. Keep up the great work sir. I experienced some difficulty when I loaded the power query to Data Model. Multiple other queries got created viz Sample Query, Transform Binary from first. How do I get rid of those and what is wrong with my model. please help?

  2. awesome videos..i developed my interest just bcoz of your videos..thanks alot

  3. PLEASE HELP!!!!
    Each month I get an Excel workbook with employee names, Regular hours, Over Time hours and Total hours (reg & OT hours summed together). I then copy those total hours into another workbook that tracks the employee name, Months (where I Paste the Total hours from the monthly data) and the cumulative annual totals. In this workbook, that I’ll call the “Employee Hourly Tracker” workbook, the employees are in the first column and the month are in the first row like a Crosstabulated table with the data in the middle.
    Copying and pasting has caused a lot of problems over the years with hours not matching up with the correct employees and not being able to add or delete employees as needed. I need to put the Monthly data into the Employee Hourly Tracker. I’ve tried this with a power query that pulls from a folder but the old files have all been save differently and I can’t get the cumulative annual totals to rollover to the next year so they can continue to be added. I've tried using the ExcellsFun videos and watched all the Highline Excel 2016 Business videos; I’ve learned a lot but the monthly workbooks and the “Employee Hourly Tracker” workbooks are all different and my power query attempts have been riddled with errors. The “Employee Hourly Tracker” goes back to 2014 and again the workbooks are save differently and filled out differently.
    I want the data source (in this case, the monthly workbooks) to fill in the “Employee Hourly Tracker” workbook. I also need each employees annual total hours to roll over to the next year and keep adding up. I wish I could share my data but that's not possible, does this make sense? Should I rebuild the “Employee Hourly Tracker” workbook the way I need it first and then try to enter in all the data(Hopefully not)? Please help, the current workbooks are so messy and only getting more convoluted has each month comes and goes. Any advise for extra help is greatly appreciated. Please Help…SOS

  4. Thank you again! Always so helpful. By the way, are you interested in making excel videos for mac? I just got my first mac book and shortcuts are little different that I always have to pause the video and look them up 🙁

  5. Awesome stuff mate. Well presented and professionally done. I'm loving every episode you make. Even if I'm sometime familiar with what you're presenting I still learning new tricks like your very useful keyboard shortcuts. Well done! Keep doing this great job you're doing and you'll help millions of people in making their job easier. Cheers!

  6. Great Video.
    @1:32:36 I used the filter and load more, but "Product" is not listed !? Therefore , I couldnot filter it out. Help please

  7. Learning some amazing stuff. If only the company I currently contract with used Excel 2016 instead of… 2010. But, they are a giant financial institution where changes comes slowly, if at all. I'm mos def going to have to watch and re-watch these to make them second nature.

  8. As an "older" person, I thought I knew a lot about Excel.  Each time I watch one of your videos, I learn so much more.  You have a great teaching style, and I appreciate you making these videos available to the general public.

  9. I would really appreciate if you can help me with this dilemma sir.

    I have the show report filter pages grayed out as well as the calculated fields and items. I have been searching for a solution but this seems to be an everlasting issue so far for excel pro 2016 with powerpivot.

    Can you please help!!

  10. You are amazing. I would cut off my left leg to get advice from you on a project I am working on because I know exactly what I want to do but clueless as to where to start.

  11. Are you gonna update your book for excel 2016, seems like a lot has changed.

  12. When I try to get multiple PivotTables from the tutorial's last PivotTable using "Show Report Filter Pages" I can't do it. The "Show Report Filter Pages" is disabled. Am I doing something wrong? Thanks for any guidance.

  13. As usual….. BEST EXCEL TEACHER….BEST EXCEL TEACHER….BEST EXCEL TEACHER….
    If allowed i would like to Thumbs up every minute i watch your lectures…
    Thanks a lottt….

  14. I have a problem with Power Query Data Model (31). I reside in Germany and it uses a different date format or my Excel/Windows has different date format than in csv. I better change my system date format right? EDIT. Yes indeed. It worked.

  15. Your videos have literally been life-changing, I cannot thank you enough!

  16. I really love your excel video, I could watch them all weekend long 🙂

  17. Mr. ExcelisFun, thanks so much for all your videos and they are the top training materials I have ever learnt from. I feel watching your training video is far more useful and fun than reading the books I bought. Do you have any books (written by you) that we can buy online? Again, thanks for all your help!

  18. hello sir, I am using Excel 365 and following with this exercise. At 1:18 in the video, i tried to update my query table with the newly added data but it couldn't update. Can you please help me?

  19. It is hard to believe one can find this high quality training on YouTube. Thank you for posting and sharing.

  20. Hey! I'm working on a Macbook and would love to use your shortcuts. I'm on 19:20 and they unfortunately are not the same as PC. Is there a "Menu Title" name for this so that I can create my own shortcut? Thanks

  21. In this one I got really excited about EXCEL. Thank you!!! Absolutely magnificent teacher

  22. These videos are awesome!

    For some odd reason on the very last portion of this exercise, after I have created the power pivot, a have a (blank) showing up on the in the regions and it is completely throwing off the pivot chart. Once I move revenue down to the values section, all revenue is appearing under the (blank) region so I cannot see the regional breakdown. Any quick solutions to this problem?

  23. I need to do analysis on data within three Excel worksheets. One worksheet is an inclusive list, while the other two worksheets are subsets of the main database, listing different operators performance and work history. The analysis I need to do is to determine if the performance of each of the operators within the subset worksheets is impacted by the attributes of each of the operator that is listed in the main worksheet (training, seniority, age, shifts, etc..). The names of the operators are present in each of the sheets, but the subset worksheet has the name of operators of interest.

    Would I be able to extract the attributes associated with each operator from the master database and add it on the same row of the operator in the subset worksheet. The idea is to determine which of these attributes has any impact on performance. I used to do this using vlookup and other formulas and would like to use the power query if possible. Please let me know what video I can watch that would illustrate a similar case.

    Thank you for all of the videos you post.

  24. The amount of effort and love that "ExcelIsFun" Mike has selflessly contributed to the World on his channel here is obvious and simply amazing. If you would like to say "thank you" in a more substantial way, please consider sending him a donation…
    Go to his: https://www.youtube.com/user/ExcelIsFun/about and use the "Donate with Paypal" link on lower left.

  25. All excel classes provided by ExcellsFun instructor are amazing. Thank you very much for providing us with the opportunity to learn and master excel.

  26. You are true EXCEL GURU, smart learning in depth and quality with time and money saved. Many sincere thanks and keep the good work

  27. This is a great video! The content is great and you do an amazing job explaining it. Thank You! I'm subscribed and staying tuned!

  28. I just want to say that this is the best Excel Training ever. You are a real passionate teacher and I am sure you have touched/altered so many lives with so much passion for teaching. God bless you! Will tell as many people as I can.

  29. Hi there, First of all thanks making these tutorial! it's amazing. But I have this problem, when I insert text files in Power Query, it opens the data into 1 column not in 4 separate columns like yours in the video. can you please help me out ?

  30. That is just amazing! Thank you very much for the tutorial, it is perfect! I look forward to use all those features 😀

  31. Just found out, I can download the excel sheet and the handout… for all lesons – you are the best!!! Thank you soooo much :-)))

  32. And now I just saw how many videos you made and fiel absolutly exciting… that is like Christmas for me 😀 😀 … I think, now I know what I will watch in the next days 😀 Thank you very much for all that work you did for all of us, realy appreciate it. Great work!

  33. wow……double wow you at the MAN…..YOU ARE MAKING ME LOOK GOOD AT WORK!!!

  34. Hey Mike, concerning the PDF: what are “unwanted charters” in example 1 ? 😉

  35. Hi Mike, I must say I do appreciate your videos full of knowledge and skills, and I believe people with careful & respectful mind will understand how much you devoted on those videos. We just see nearly two hours filming, but behind those work are the unspeakable hardworking. I feel hurt when I saw the thumbs down, too vicious and rude. I learned a lot from your videos which I cannot get from all classes I've ever had. Sometimes the world is not that moral as it should be, but the unselfish people, like you, will be blessed. You are a real MASTER.

  36. Could you see my video about Excel' function BS_VLOOKUP for finding and return array;
    https://www.youtube.com/watch?v=YsmBrlwigco

  37. You are the best!!! Thank you so much for putting all that time into teaching us!! I've been watching your videos since I was in college. I am now a Senior Data Analyst and I still watch them!! haha Keep up the awesome work and thanks again Mr. ExcelIsFun!

  38. for an existing Table in my work sheet -I tried to do "Data-Get & transform-from Table", then I did few operations in the query, like replace values and "unpivot other columns" operation on other columns by selecting first column of the query (the query looked ok with lot of rows created), later did close and load to table in new worksheet, but got the error " Initialization of the data source failed, check the database server or contact your data base administrator , Make sure the external database is available and then try the operation again. If you see this message again, create a new data source to connect to the data base." This table is required very urgently to compile a dash board for a training assignment (for all employees) in my office and its very urgent, thus it is kindly requested to assist me to over come this issue,(Even our IT dept is not aware of any related issues)(I am using Excel 2016) Thanks in advance Regards Sandeep

  39. ExcellsFun is the best excel tutorial available on Youtube. Thank you Mike.

  40. sir, Good morning, your King in Excel, God bless you, and your Family member sir,

  41. Dear Mike,
    May I ask you a question about the Excel pivot table filter function? I have a pivot table with the whole nation's figure and it is connected with the external data source in our head company in another country. I would like to save pivot tables for each individual state which means every time I open one particular pivot table, I only want to see the figures for that state. But the filter function seems not functioning as every time I refresh the file, the filter will vanish and figure will be for the whole nation's. I tried different setting under the pivot table settings but it's not working. After it refreshing with the server's data, the previous saved filter will be gone. Could you please help me with this question or it is the setting problem in the OLAP Cube instead of our local one? Really appreciate for your help.
    Best regards
    Emma

  42. How do you fix when the Sales Rep split into two columns? Last name, first name…

  43. The other Alt keyboard for sort dialog box is kinda hilarious

  44. I taught Excel(as well as many other computer subjects) in a technical college at a much lower level than this from 2000 – 2015. I've always enjoyed working with it. Now I have a job where I actually use it many hours a week for a variety of tasks. These videos have lifted my skills enormously. Now that I have a real use for pivot tables etc I can finally get my head around it properly. I've looked at many training resources over my career and these are among the best I've found.

  45. Hello,
    The attached excel file belove on the video and excel file which is used in video is different. Can you give me the link where i can download File which is used in video ?

  46. This is Amazing it's easy to understand . Great teacher , thank you so much

  47. you 've made the world better by sharing your knowledge for free.Plus, as guys said, you're such a grreat teacher..A big thanks from Viet Nam

  48. Thank you for such a good video. I have a quick question. When we do Vlookup, only the first value in the lookup array is fetched and the rest are ignored. While forming relationships in Power Pivot, all the values are included in individual rows. Is my understanding of difference between the two correct?

  49. Mike, I have no words to express how much you are good. I just say God bless you!

  50. I have watched a lot of Excel channels, nobody is even close to Mike! Simply you are the best.

  51. I watched this video like three times. It is great! Thanks Mike. Is Masterexcel.net your website?

  52. You are a gift to the world with your tutorials. I love them greatly.
    May you please assist me. I want to show % growth of this year sales over last year sales in pivot table but on month on month like January 2018 % growth over January 2017 .

  53. Hi sir and Excel expertise I like to ask you one thing I am having data with alphanumeric product data so I want to extract only alphabets form that alphanumeric
    Example

    Ab25cdf=abcdf

    Gh78v10y=ghvy

    Zxvu547tf=zxvutf

    Please help

  54. Hello, is there any method to import using powerquery the result of a pivot table to unpivot it and get a table of the result shown by the pivot table? Crazy thing but i need to report the resullts of diary analisys avoiding to import all the amount of data i used every day and only get from every diary pivot table resullts and append them into a shorter table.

    Congratulations, great channel. The most comprehensives explanations on YouTube!!!

  55. Some update ago they thankfully made sure to put the power query table result right before your active sheet so you don't have to move it around anymore.

  56. When I creating the Pivot table using data models ,once i dropped the Region under 'Row' there is "Blank" in the last cell and while i dropped the 'Revenue' under 'Values' sum of revenues showing against the blank all other region fields disappear ,i checked the region column in Query editor and manage data window using filter option there is no Blank value there .How to rectify this, please advice.

  57. I've just started off my internship where I was loosely introduced to (for me unknown) Power Query and Data Model tools. I was kind of nervous feeling instantly unqualified. But thanks to you tomorrow I can go in with much more confidence and a basic understanding of those features. KEEP UP THE GOOD WORK! Thank you!

  58. Thank you very much for this series! I feel that I will be at least an intermediate use of excel after studying this playlist. Thank you!

  59. hi ,for homework 5,i tried to use vlook up to make another column but it didnt work out i dont why ?

  60. Sir, mine is excel 2016 home and student version, i checked in "Add-in" but didn't had power pivot option, how do i install power pivot in my excel.

  61. This is great work. The tutorial is great and the way of explaining is great it is very helpful to understand excel, simply great work.

  62. From time to time, I like to come to your channel to re-watch your excel videos to resharpen my Excel skill. This month my target is to finish watching all your advanced excel classes videos and completing all given homework. I am looking forwards to your another Epic video series on " Excel 365", I know this is on your agenda and I cannot wait to learn it from the master!

  63. I've been following you over two years. Of all, you are the best excel teacher I can learn from! Gracia!!!!!

  64. help!, when I do a sort for dates in quartiles the cells disappear (it's not selective)

  65. Mike, Thanks for your titanic job!!!
    "Oh, how many blissful revelations

    The spirit of enlightment hides!

    And then experience born of lapses

    And genius antinomy-wise

    And chance, the heavenly inventor…
    "
    A. Pushkin

Leave a Reply

Your email address will not be published. Required fields are marked *