Excel - What is Power Query in Excel - Episode 2037

Microsoft Excel Tutorial: What is Power Query.

Welcome to another episode of the MrExcel Podcast, where we dive into all things Excel. In this episode, we will be discussing how to clean data with Power Query. As always, you can find the entire podcast series in the playlist by clicking the "i" in the top right-hand corner.

In this episode, we will be focusing on Power Query, which is built into Excel 2016 under the "Get & Transform" tab. However, if you are using Excel 2010 or 2013 on a Windows computer, you can download Power Query for free from Microsoft. Our goal for today is to create a list of all the files in a specific folder and then manipulate the data to make it more useful.

To begin, we will need to copy and paste the folder path from Windows Explorer into Power Query. This will give us a preview of the data, which we can then edit to our liking. For example, we can add the file size attribute to our list of files by expanding the attribute field. We can also remove any unnecessary columns, such as the date created, by right-clicking and selecting "Remove."

But that's just the tip of the iceberg. We can also use Power Query to combine data from multiple CSV files into one Excel grid. This is done by clicking the two down arrows next to the horizontal line, which will pull in every record from every file in the folder. This is an amazing feature that used to require a VBA macro, but can now be done in just a few clicks.

We can also use Power Query to convert text into dates, which is incredibly useful when working with large amounts of data. And the best part? Power Query keeps a record of all the steps we take, making it easy to go back and make changes if needed. And if the data in the folder changes, we can simply refresh the query to update our list.

In conclusion, Power Query is an incredibly powerful tool that can save you time and effort when working with data in Excel. It's already built into Excel 2016, but can also be downloaded for free for Excel 2010 and 2013 on Windows computers. And if you want to learn more about Power Query, I highly recommend the book "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar. It's the best resource for mastering the Power Query interface and will greatly enhance your data cleaning skills. So don't wait any longer, start using Power Query today and see the amazing results for yourself.

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #walkthrough #evergreen #powerquery #excelspeak #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp

Table of Contents:
(00:00) Introduction to Power Query
(00:12) Downloading Power Query for Excel 2010/2013
(00:37) Listing Files in a Folder
(00:51) Manipulating Data in Power Query
(01:06) Adding and Removing Columns
(01:21) Using Attributes to Add More Data
(01:31) Refreshing the Query
(02:07) Comparing Data from Different Folders
(02:23) Advanced Features of Power Query
(02:46) Combining Data from Multiple CSV Files
(03:19) Using Applied Steps for Undo and Audit Trail
(04:17) Simplifying Data Manipulation with Power Query
(05:00) Creating a PivotTable from Power Query Data
(06:21) Converting Text to Dates in Power Query
(07:01) The Power of Applied Steps
(07:25) Refreshing Data in Power Query
(08:38) The Benefits of Power Query
(09:01) Recommended Book: "Master Your Data"
(09:31) Clicking Like really helps the algorithm

This video answers these common search terms:
how to combine all excel files in a folder with power query
how to consolidate from workbooks in excel
how consolidate multiple workbooks in one workbook excel
how to consolidate multiple excel workbooks into one
how to combine workbooks excel
how to combine workbooks into once excel sheet
is powerquery and add on to excel
is power query available in excel in 2013
is power query in excel 2016
is power query in excel?
what is power query editor in excel
what is power query in excel
where is power query in excel
where is power query tab in excel
how to add power query to excel 365
how to use power query in excel youtube
how to open power query in excel
how to setup power query in excel
how to start power query in excel 2016
where is power edit query in excel
how to add the power query add in to excel
how to install power query in excel
how to use excel power query editor
what version of excel has power query
how to add a excel table into powerquery
how to unpivot data in excel power query
in excel how to clean the with power query
how to merge two tables in excel using power query

[DougHExcel]

null -> Fill values...priceless tip! Thanks Bill.

[cambike]

I do not advocate swearing on YouTube but Holy Sh1t... That is probably the best information ever in such a small time window. Love your style. Thanks

[michealreynolds]

Bill, this is a great video that shows off the power of Power Query. To everyone that wants to do amazing things in Excel, get both books ("XL" and "M"). You will get more done in less time.

[BillSzysz]

Thanks for promoting PQ :-)))
You do not need to load the Power Query result to the sheet. ("keep conection only" is enough)
Then you can build PT on this conection (external data source).
The only you have to do is refreshing the Pivot Table.(once, instead of two if you load the query to worksheet).

[excelisfun]

Power Query is awesome! Great video with tons of cool tricks! Thanks!

[kurtturner]

I wanted to thank Bill for looking into this for me. Most of the gurus on line don't take the time to do such a comprehensive reply, but Bill did. He's one of the best resources the internet has to offer for excel related topics. I appreciate all of the info! In my case, the replace in place macro did the trick. Please also send my thanks to your guru buddies!!! PS I'm sorry for not getting back to you on one of the questions you had. I must have missed that email. Kurt

[MonkEBoy-udkj]

Hi Bill! I’ve been having to teach myself Power Query to help out at work, and when I saw this I thought “but what happens next year”? So if you’re interested, I would suggest using the “created on” column to split off the year (and day? Or just use the 1st, depending on your preference), and combine it with the month column in the data. That way you can still use it next year. 🙂

[linelson]

Hey Bill, Power Query deserves a exclusive playlist in your channel.

[BrianDangmrdang]

HOLY MOLY Unpivot and fill down are game changers--and that was done in the first half of the video O_O

[osielg]

Hello Bill, that does not go with what is explained in the video, but you can extract or convert a text line date. Example: payment of Q200.00 for contribution to price when purchasing code 16395 from November 17 to 19, 2018.
Thank you very much for your comments. :)

[Steve_WIEM]

Every time I do something in Power Query I am amazed at the power.

[giordanirivas]

Bill, what if you have sales, percentages and multiple in this table. I guess the we would have three different value columns especially if then I need to graph the data and use slicers to filter data? How to do? Thanks.

[zw]

First Great video.
I bought the datamonkey book and i must admit it contains a lot of information that is usefull for day to day simple things. Where i do not agree is it teaches you everything about power query. I run into issues on a day to day basis where i need to use PQ functions like or Table.... and i can't find any help on that . In order to build a good power query the available functions should be explained with real life examples not syntax related explanation. Your demonstration A+ power query is the best thing that happened to me no more labor intensive VBA just build once Use Often.

[Luciano_mp]

Bill, muito bom, grande tutorial.Obrigado.

[dorischoo]

Hi Bill, I am a Microsoft 365 subscriber. I successfully added Power Pivot, however unable to find Power Query. I checked in Data menuData, there is the Get and Transform sub group, but still not seeing Power Query in the Menu header. Can please advise. Am confused, thanks.

[BestRestaurantToEat]

What happen in 2017, you have to redo from the year onwards

[mattmatt]

Can you combine xls files just like csv files (by clicking double arrow) in the latest version of Power Query (or Power BI), or you have to create a custom column ?

[Osmanfayyaz]

In Power Query, I am unable to enable M Intellisence
Can you guide me
I have subscription based office with semi annual channel Update
and Particular update came on 14 Jan-2020 by Microsoft
At the moment I have latest build 14 Apirl
In Power Query Editor, I am unable to find M Intellisence whether I have to install something (any specific software)

[ficoescalante]

Hi Bill. How can I clean and consolidated tables into from one single workbook into a single data base. If you answer to my email, I can forward to you the xls file. Thanks

[achusp]

Hi
Can you please tell me how to split data like this

Respondents Question
Respondent 1 A;C;E
Respondent 2 A;B;C
Respondent 3 A;C;D


INTO THIS

Respondent Question 1
A B C D E
Respondent 1 1 0 1 0 1
Respondent 2 1 1 1 0 0
Respondent 3 1 0 1 1 0

Would he a HUGE help. Thanks in advance