I can't emphasize just how good a job you do presenting this information. Everything about this is perfect, the pacing, the attention to details and thoroughness, the progression, the actual use and story of it, diagrams.. Plenty I haven't touched on. Thank You so much for sharing this, you must have improved a great many lives. I'm so happy I don't have to feel the life draining out of me like so many other videos out there!
Thank you for including the "why" and not just steps! It makes things easier to understand and apply in different situations. As an educator that's one of my pet-peeves, and you do a fabulous job. I wish I'd found your video first, and I look forward to seeing more videos.
Omg can’t believe I finally found you I’ve watched 5 vids so far and everything is just so extraordinary, super simple to understand. You’re the best instructor I’ve ever seen in my 29yrs of living
Others say it and I wholeheartedly agree - spend a few minutes in Excel Campus and you'll get the basics that are clear are easy to remember. When someone learns or retains something 'easily' it's in part down to the teacher Really knowing the subject. Thank you 🎉
Awesome job consolidating all this info into fifteen minutes! If someone is experienced with Excel they really don't need a three hour introduction to understand what Power Query is. Too much information is overwhelming, which keeps us from even trying. After watching this I finally "get it" and am ready to try this the next time I clean up data. Thank you so much!
Having seen videos on PowerQuery presented by other people, I have learned a lot from you, as I like the way you organized & presented your material, which made it easier for me to understand.
Brilliant. This was perfectly presented. I have done the same thing over and over again. I never knew the refresh option works like that. I can’t wait to improve my workflow to do this. Thank you!
Thanks for sharing! Excel has evolved into a such powerful data tool ! It’s basically becoming an agile version of a mixture of power BI, Access, and traditional excel !
This was such an extraordinary video which made me understand the very basics of power query. I also appreciate your communication which is crystal clear, no repetitions and great with your subject matter. More than everything I really appreciate creating such educational videos which help people like us to get knowledge free of cost. Thank you so much.
Jon, this video is a crystal-clear explanation and demonstration of a very powerful Excel feature. Very impressed by the quality of this video, have now subscribed as the Power Pivot and Pivot Tables parts look interesting too. I already use Pivot Tables but I'm sure your video will fill in any gaps on my knowledge.
This is great. i watched hours of videos on this topic months ago and battled through many of the ins and outs you described. Your video summarizes the business reasons behind this type of analysis so well. Thank you for taking the time to present this in such a digestible manner. I'll be taking your webinar at the first opportunity.
The 'why' was long overdue. Excellent presentation as always. A waaaaaaaay back I didn't connect with the removal of columns in the process steps as it was never explained. It took a bit and then the lights went on, lol. I don't use PQ as much as I should, more because I am so rushed in what I need to get done and have a hard time squeezing in the extra timing time and practice. However, as time marches on it becomes easier and easier to use the product. Thank you.
Thank you soo soo much Jon for this free webinar and lectures you do. You are Amazing and I cannot wait to enroll on one of your programs fairy soon. Your teaching is to the point. This video really helped me on a difficult rushed project today. See you on one of your courses very soon, From UK.
I love your videos. Explanations are understandable and to the point. I am an older follower, do a lot of stats for non-profits as volunteer, and I work on Mac OS. Sometimes it is difficult to apply your videos, but I will try with this one because it will save me tons of time! Thanks.
Thanks Lucia! I appreciate your support. In regards to Power Query on the Mac, it is not fully available yet for the Mac version of Excel. However, Microsoft is working on making it available and currently in phase 1 of the project. This means you can only refresh queries on the Mac. You cannot create or edit them yet. Here is an article that explains more (techcommunity.microsoft.com/t5/excel-blog/get-amp-transform-power-query-in-excel-for-mac-phase-1/ba-p/876840) One workaround is to install a virtualization app like Parallels (www.parallels.com/) on your Mac. This will allow you to run Windows on your Mac and install the Windows version of Excel. If you have an Office 365 subscription then the Windows version of Office will just count as one of your users. You will then be able to use all "power" tools including Power Query, Power Pivot, Power BI and full VBA. I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks for the question Lucia and thanks for your response Jon. I've been sitting on my Mac for days trying to figure out how to Get & Transform and apparently it's not available yet. You both saved me some time and maybe a couple headaches.
Nice video, thanks! So, I'm a long-time SQL user, but I am trying to put together an Excel spreadsheet without an SQL database behind it for some folks who don't have access to an SQL database. I have a few simple tables that I want to join together, and use like an SQL materialized view. I've played with putting each table in a sheet, then using Power Query to join them in a new sheet. This works fairly well. And I find that indeed if I change data in the source tables and hit "Refresh All", the joined data updates nicely. Next thing I tried was saving the Excel workbook, exiting Excel, then opening the workbook again. Then I wanted to repeat the exercise: update one of the source tables, then hit "Refresh All" to see the joined data automagically updated. This doesn't work, though; Excel complains about connections. So here's my question: Can one make re-establishing connections automatic, so that there aren't any extra steps when reopening the workbook?
What about inconsistent column headers ?? For example- we have a tool from which we can pull data but sometimes we add new columns so let’s say “last name” was column B - but the following download you’ll find it in column C- do you have to manually clean up the spreadsheets before combining??
At 4:59, what if I want to define the decimal point (could be either , or .)? In the old version the user could do that from the beginning, where is this now?
Great video! What Id like to know is when you close the sheet where you merge the files, where does it go or is it something that you can save as an Excel sheet? 2. You show how to combine the results in one sheet, what if I want the Applied Steps to be applied on individual sheet because the report should be separate instead of merge reports. This is for my daily and weekly reports. Thanks!
This is a brilliant tutorial, thank you very much! Despite your clear explanations, I'm still experiencing endless problems with the Date format. Whenever I get CSV data with date fields into Power Queary, and then try to convert that to Date format, it reverts to the US mm/dd/yyyy format. I need it to be dd/mm/yyyy format. I've checked Regional settigns in the Control Panel, the locale settings in Power Queary, but nothing works. Even the Column from Example technique does not work. Please help?
thank you it's so useful. I've created an excel 'From Workbook" and did some tasks on it, and now I wanna change its source to "From Folder". How do I do that?
This put me into this wonderful concept during the lockdown time where we hardly had any software loaded in our laptop. This is awesome and magic bullet. But, tell me weather it can handle calculated columns
Thanks for that crisp session.My query is if salesman is assigned to particular store which is mapped to current customer master.Assume someone left the co or replaced.if the master file is updated.can it be replaced across file.Guide me on this
Hi, I am new to power query , so that I have few basic questions ! Appreciated if you can answer 👍🏻 1- is it necessary the source data should be in table format? 2- is it necessary to have the same name for the work sheet of all excel files ? ( when I tried to do a query from folder 1st time it didn’t load full data whereas when I changed the sheet names all same it successfully loaded) 3- My files are named as months, the PQ takes the Feb month file first and few others are also irregular in order, what should I do to make them in order?! 4-at my work I use MS Office 2016 (standard) version , where I have PQ option at Data tab, and I have “From Folder” option as well , but when select the folder it also shows all the files in the folder and then I press “OK”, In the preview window I am not getting any option to proceed! Like your window at 04:38 without the options below to combine and transform etc!! Am I missing something ? Do I need to do anything to be available in MS 2016?! Thanks in Advance
In my job I receive a lot of spreadsheets that have columns named slightly differently. For example one spreadsheet would have a column named Address 1 and another spreadsheet would have that column named Location 1. I want to stack them on top of each other as you showed in this video, but would this work with such messy data as mine? Because as of now I am manually renaming columns, dragging them to match certain orders, etc. Is there a way to automate that part of the data cleaning process?
Great question Jake! Yes, this is possible with Power Query. There are many different ways to go about it. I'm working on a new video that explains one possible solution with multiple sheets in multiple Excel files and the columns do not have to have the same names in the header rows. We'll be publishing that video in the next few weeks. We also cover this process in detail in our Combine Data with Power Query Course, which is part of our Elevate Excel Training Program (excelcampus.com/elevate). I hope that helps. Thanks again and have a nice weekend! 🙂
Hey Jon. I am trying to create a budget for my depreciation from a capital budget. I run my budget with Jan - Dec across the top of the sheet. What I want to have is headers say Asset, Month Purchased, $ Capital. If for example, I put July under the column header, i want the monthly depreciation to populate in the months of July - December. I thought Vlookup would handle this, but I am at a loss. Do you have a video covering that type of query ?
Mr. Jon I have a concern of power query , say for example data in new division columns is not in same order of earlier divisions Now will power query s automation be useful???
Hey Jon, Thanks for the good explanation.i have a question though.sometimes i get Back a value more than ones after loading the datas eventhough i have that specific data only once in my source file. Are u familiar with this Problem?
I've been playing with this feature and noticed irregularities in my originating table of information. Maybe I am doing something wrong, but records would start changing on the first table when merging the additional tables. Even as connection only. So I reverted back to 10+ VLOOKUPS in the meantime.
How can one or two column cell values be formatted as Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?
I can't emphasize just how good a job you do presenting this information. Everything about this is perfect, the pacing, the attention to details and thoroughness, the progression, the actual use and story of it, diagrams.. Plenty I haven't touched on. Thank You so much for sharing this, you must have improved a great many lives. I'm so happy I don't have to feel the life draining out of me like so many other videos out there!
Glad you enjoyed it! Thanks! 😊
"feel the life draining out of me!" 😁👍. Yep-same here but you described it perfectly!
Thank you for including the "why" and not just steps! It makes things easier to understand and apply in different situations. As an educator that's one of my pet-peeves, and you do a fabulous job. I wish I'd found your video first, and I look forward to seeing more videos.
Ditto. I am a data analyst with a master's degree in Education. Jon has hit all the important instructional bits to make his instruction successful.
Omg can’t believe I finally found you
I’ve watched 5 vids so far and everything is just so extraordinary, super simple to understand. You’re the best instructor I’ve ever seen in my 29yrs of living
Others say it and I wholeheartedly agree - spend a few minutes in Excel Campus and you'll get the basics that are clear are easy to remember. When someone learns or retains something 'easily' it's in part down to the teacher Really knowing the subject. Thank you 🎉
Wow! Thank for your kind words @fanp1188 ! 😀
Awesome job consolidating all this info into fifteen minutes! If someone is experienced with Excel they really don't need a three hour introduction to understand what Power Query is. Too much information is overwhelming, which keeps us from even trying. After watching this I finally "get it" and am ready to try this the next time I clean up data. Thank you so much!
Having seen videos on PowerQuery presented by other people, I have learned a lot from you, as I like the way you organized & presented your material, which made it easier for me to understand.
100% agree. really good presenter
Brilliant. This was perfectly presented. I have done the same thing over and over again. I never knew the refresh option works like that. I can’t wait to improve my workflow to do this. Thank you!
Great to hear that, Pete! 😀
Thanks for sharing! Excel has evolved into a such powerful data tool ! It’s basically becoming an agile version of a mixture of power BI, Access, and traditional excel !
Thanks Bruce! Yes, great point about the evolution of Excel. I love it! 👍
wow, excellent. i used think that it is very hard to learn excel but after watching your videos, i have learned so much. Thank you
Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!
This was such an extraordinary video which made me understand the very basics of power query. I also appreciate your communication which is crystal clear, no repetitions and great with your subject matter.
More than everything I really appreciate creating such educational videos which help people like us to get knowledge free of cost.
Thank you so much.
Jon, this video is a crystal-clear explanation and demonstration of a very powerful Excel feature. Very impressed by the quality of this video, have now subscribed as the Power Pivot and Pivot Tables parts look interesting too. I already use Pivot Tables but I'm sure your video will fill in any gaps on my knowledge.
Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.
You have an excellent way of explaining /training. I’ve learned so much in the first two videos I watched today and can’t wait to put them in practice
This is great. i watched hours of videos on this topic months ago and battled through many of the ins and outs you described. Your video summarizes the business reasons behind this type of analysis so well. Thank you for taking the time to present this in such a digestible manner. I'll be taking your webinar at the first opportunity.
The 'why' was long overdue. Excellent presentation as always. A waaaaaaaay back I didn't connect with the removal of columns in the process steps as it was never explained. It took a bit and then the lights went on, lol. I don't use PQ as much as I should, more because I am so rushed in what I need to get done and have a hard time squeezing in the extra timing time and practice. However, as time marches on it becomes easier and easier to use the product. Thank you.
Thank you soo soo much Jon for this free webinar and lectures you do. You are Amazing and I cannot wait to enroll on one of your programs fairy soon. Your teaching is to the point. This video really helped me on a difficult rushed project today. See you on one of your courses very soon, From UK.
We are blessed with people like you
The most insightful Power Query intro. Thanks Jon for the share, keep them coming.
amazing as always!!! thanks for all your assistance over the years
You have made such a difficult Excel topic easy to understand. thank you
One of the best videos introducing to Excel Power Query. Well done.
Glad you liked it! 😀
Great, its really valuable presentation for skills enhancement. Thank you very Much
Really got engage with the way you explain your videos. Very organize and precise information. Kudos!
Glad you like them! 😊
i follow you and ExcelIsFun! you guys are my heroes!!!
I love your videos. Explanations are understandable and to the point. I am an older follower, do a lot of stats for non-profits as volunteer, and I work on Mac OS. Sometimes it is difficult to apply your videos, but I will try with this one because it will save me tons of time! Thanks.
Thanks Lucia! I appreciate your support. In regards to Power Query on the Mac, it is not fully available yet for the Mac version of Excel. However, Microsoft is working on making it available and currently in phase 1 of the project. This means you can only refresh queries on the Mac. You cannot create or edit them yet. Here is an article that explains more (techcommunity.microsoft.com/t5/excel-blog/get-amp-transform-power-query-in-excel-for-mac-phase-1/ba-p/876840)
One workaround is to install a virtualization app like Parallels (www.parallels.com/) on your Mac. This will allow you to run Windows on your Mac and install the Windows version of Excel. If you have an Office 365 subscription then the Windows version of Office will just count as one of your users. You will then be able to use all "power" tools including Power Query, Power Pivot, Power BI and full VBA.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks for the question Lucia and thanks for your response Jon. I've been sitting on my Mac for days trying to figure out how to Get & Transform and apparently it's not available yet. You both saved me some time and maybe a couple headaches.
Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.
Thank you for simplifying a very complex topic.....
You're welcome, Ben! :)
Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!
Great and amazing tutorial. John!
Very clear explanation, easy for beginner to start with.
This is great!! Thanks :D I feel like I've spent soo much time cleaning data and never cared for power query :( I was dumb to think that
Very concise, simple and informative. Great video!
Great presentation. Logical steps and easy to undertstand
This is awesome! So happy I found your page 😀
Short ,smart and to the point
Thanks ☺️
Thank you Abo! 🙌
This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!
Thanks man, very clear presentation and very helpful !
Very, very informative. Thank you
Really good and clear as usual. Thanks a lot.
Nice video, thanks! So, I'm a long-time SQL user, but I am trying to put together an Excel spreadsheet without an SQL database behind it for some folks who don't have access to an SQL database. I have a few simple tables that I want to join together, and use like an SQL materialized view. I've played with putting each table in a sheet, then using Power Query to join them in a new sheet. This works fairly well. And I find that indeed if I change data in the source tables and hit "Refresh All", the joined data updates nicely. Next thing I tried was saving the Excel workbook, exiting Excel, then opening the workbook again. Then I wanted to repeat the exercise: update one of the source tables, then hit "Refresh All" to see the joined data automagically updated. This doesn't work, though; Excel complains about connections. So here's my question: Can one make re-establishing connections automatic, so that there aren't any extra steps when reopening the workbook?
I will be using this in ,multi data disciplines. Thank you... more power at my work place!
Very clear and concise presentation. I'm eager to give it a go. Thanks again.
Amazing tool and amazing presentation! Very easy to follow.
You are the best. I am naiem from Bangladesh.
Excellent and effective presentation. Thank you!
You're very welcome! 😊
Excellent session and tutorial, thanks!
An absolutely superb presentation, thank you.
Useful tutorial. Thank you!
Awesome. I love your detailed expression and they are easy to learn.
Glad you love them! Thank you!
An awesome introduction tutorial and a very good explanation. Thank you!
Thanks! :-)
Very well explained! You make everything sounds so simple!
Very professionally presented.
What about inconsistent column headers ?? For example- we have a tool from which we can pull data but sometimes we add new columns so let’s say “last name” was column B - but the following download you’ll find it in column C- do you have to manually clean up the spreadsheets before combining??
Concise demonstration. Thank you.
great video! power query is a major time saver
Excellent introduction - Thanks Jon
Thank you Lindsay! I appreciate your support! 🙌
Your videos are excellent!!!
Thanks for this helpful tutorial.
Great Tutor. Amazing Presentation.
Thank you, you are my excel hero! 😘
Very useful. Thank you!
At 4:59, what if I want to define the decimal point (could be either , or .)? In the old version the user could do that from the beginning, where is this now?
Great video! What Id like to know is when you close the sheet where you merge the files, where does it go or is it something that you can save as an Excel sheet? 2. You show how to combine the results in one sheet, what if I want the Applied Steps to be applied on individual sheet because the report should be separate instead of merge reports. This is for my daily and weekly reports. Thanks!
8:29 you could just change type of the column with 'locale' and then follow with another step with 'date'
Thank you for sharing this 🙏
Thanks, John for the awesome tutorial.
Your presentation is amazing!!
Excellent thank you for this presentation.
Thanks for tutorial.Its really helpful
Wonderful presentations Jon!. I learned a lot from this
Just great video many Thanks.
This is a brilliant tutorial, thank you very much! Despite your clear explanations, I'm still experiencing endless problems with the Date format. Whenever I get CSV data with date fields into Power Queary, and then try to convert that to Date format, it reverts to the US mm/dd/yyyy format. I need it to be dd/mm/yyyy format. I've checked Regional settigns in the Control Panel, the locale settings in Power Queary, but nothing works. Even the Column from Example technique does not work. Please help?
Awesome video. Thank you so much!
Great video
Thank You, It is really a important
thank you it's so useful. I've created an excel 'From Workbook" and did some tasks on it, and now I wanna change its source to "From Folder". How do I do that?
Thank you so much. Clear and concise, among the best tutorial i could find!
This put me into this wonderful concept during the lockdown time where we hardly had any software loaded in our laptop. This is awesome and magic bullet. But, tell me weather it can handle calculated columns
Excellent Guide!
Well explained and presented.
Thanks for that crisp session.My query is if salesman is assigned to particular store which is mapped to current customer master.Assume someone left the co or replaced.if the master file is updated.can it be replaced across file.Guide me on this
thanks, fantastic work
man, this is poweful! Great Vid!!
I appreciate this lesson!
Glad to hear that! 😀
Many many thanks. New and beneficiary to me.
Hi, I am new to power query , so that I have few basic questions ! Appreciated if you can answer 👍🏻
1- is it necessary the source data should be in table format?
2- is it necessary to have the same name for the work sheet of all excel files ? ( when I tried to do a query from folder 1st time it didn’t load full data whereas when I changed the sheet names all same it successfully loaded)
3- My files are named as months, the PQ takes the Feb month file first and few others are also irregular in order, what should I do to make them in order?!
4-at my work I use MS Office 2016 (standard) version , where I have PQ option at Data tab, and I have “From Folder” option as well , but when select the folder it also shows all the files in the folder and then I press “OK”, In the preview window I am not getting any option to proceed! Like your window at 04:38 without the options below to combine and transform etc!! Am I missing something ? Do I need to do anything to be available in MS 2016?!
Thanks in Advance
In my job I receive a lot of spreadsheets that have columns named slightly differently. For example one spreadsheet would have a column named Address 1 and another spreadsheet would have that column named Location 1. I want to stack them on top of each other as you showed in this video, but would this work with such messy data as mine? Because as of now I am manually renaming columns, dragging them to match certain orders, etc. Is there a way to automate that part of the data cleaning process?
Great question Jake! Yes, this is possible with Power Query. There are many different ways to go about it. I'm working on a new video that explains one possible solution with multiple sheets in multiple Excel files and the columns do not have to have the same names in the header rows. We'll be publishing that video in the next few weeks. We also cover this process in detail in our Combine Data with Power Query Course, which is part of our Elevate Excel Training Program (excelcampus.com/elevate).
I hope that helps. Thanks again and have a nice weekend! 🙂
@@ExcelCampus Thank you, Waiting for the video :)
very good tutorial! thank you
Glad you like it! :)
Thank you so much Jon. Very appreciated your knowledge sharing 👏
Thanks so much K! 🙌
excellent presentation
Hey Jon. I am trying to create a budget for my depreciation from a capital budget. I run my budget with Jan - Dec across the top of the sheet. What I want to have is headers say Asset, Month Purchased, $ Capital. If for example, I put July under the column header, i want the monthly depreciation to populate in the months of July - December. I thought Vlookup would handle this, but I am at a loss. Do you have a video covering that type of query ?
Great video!. Please share sample data to practice.
Mr. Jon I have a concern of power query , say for example data in new division columns is not in same order of earlier divisions
Now will power query s automation be useful???
So helpful!!!!
excellent teaching
Hey Jon,
Thanks for the good explanation.i have a question though.sometimes i get Back a value more than ones after loading the datas eventhough i have that specific data only once in my source file.
Are u familiar with this Problem?
I've been playing with this feature and noticed irregularities in my originating table of information. Maybe I am doing something wrong, but records would start changing on the first table when merging the additional tables. Even as connection only. So I reverted back to 10+ VLOOKUPS in the meantime.
How can one or two column cell values be formatted as Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?
Darn, I could have saved a lot of time on friday if I watched this video earlier!
Effing same! Lil