Power query is a great tool built by Microsoft that will help you work with data in Excel. This tool is great for connecting to various external data sources, querying and transforming data, or cleaning and parsing data.
In this Power Query tutorial, we introduce you to Power Query, get you familiar with the Power Query Editor, teach you how to import multiple files and data. Power Query is a technology embedded in Excel and other Microsoft products designed to help you shape your data. In Excel, select the Data tab on the ribbon to see the Get & Transform Data and Queries & Connections groups. Power Query works across several Microsoft products, so whatever you learn for Power Query in Excel can be applied to Power BI and other products. Adobe premiere elements 2020 features. Custom connectors. Easily extend Power Query by creating your own connectors and data transformations if. Simple steps explains How To Install Power Query For Excel 2010 & 2013Power Query Add-in Excel 2010 & 2013https://www.microsoft.com/en-us/download/details.as. Note: Microsoft Office 2010 and 2013 Professional Plus, Office 365 ProPlus or Excel 2013/2016/2019 Standalone have the full Power Query feature set. All other desktop SKUs have the full Power Query feature set, except the following data connectors: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase.
- Web pages, Facebook
- Excel, CSV, XML, Text or Hadoop (HDFS) Files
- A Folder
- Various databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc…
This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2016.
You can download Excel Power Query here from Microsoft.
Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2016 in order to use this feature as it’s not available for previous versions of Excel. Mac user are also out of luck.
There are both a 32-bit and 64-bit versions and which one you choose will depend on the version of Excel which you have installed.
Power Query Excel 2010 Install
Excel 2010
To check what version you have:
- Go to the “File” tab.
- Go to the “Help” section.
- Here you will see the product version, if it says professional plus 2010, then you’re in luck.
- Here you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.
Excel 2013
To check what version you have:
- Go to the “File” tab.
- Go to the “Account” section.
- Here you will see the product version.
- Click on the “About Excel” button.
- In the screen that pops up, at the top you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.
Excel 2016
Power query comes pre-installed in Excel 2016 but has been renamed to “Get & Transform” and is under the Data tab in the ribbon. If you have Excel 2016, then you don’t need to do anything to use it.
Install Power Query
Download The Add-In
Go to the Microsoft website:
- Select your preferred language.
- Click the download button.
Select The Correct Version
Install Power Query Excel 2010
- Select either the 32-bit or 64-bit version depending on your version of Excel.
- Click the next button.
![Power query excel 2010 download Power query excel 2010 download](/uploads/1/1/0/4/110432863/870953252.png)
Run The Setup Wizard
Make sure Excel is closed and then click the download from the download bar from your browser.
Follow The Setup
Follow the steps in the Setup Wizard.
Power Query Is Now Ready To Use
Now the next time you open up Excel, Power Query will be available to use under its own tab.
We noticed it first on April 20, 2021.. the Get & Transform Data group's 'From Table/Range' changed to 'From Sheet'. Hover over the tooltip, and you will discover that they now support arrays!This is great news. Power Query is the best thing to happen to Excel. Dynamic Arrays are another great improvement in Excel. The situation that they could not work together before this was unfortunate. Kudos to the Power Query team for providing the functionality!
Not that this is currently released to the 'Beta' channel (Formerly known as Insiders Fast). It will take some time to roll out to all Microsoft 365 customers.
Learn Excel from MrExcel Podcast episode 2400. Power query finally supports dynamic arrays.
Welcome back to MrExcel netcast. I am Bill Jelen. Something exciting today.
Look on the Data tab in Insiders Fast.
The old “From Table or Range” has now been named “From Sheet”.
Create a new query from the selected table, named range, or array in this workbook.
It used to be arrays wouldn't work with power query.
Great news, alright – so they've now added support for. Arrays. I have an array here using the SEQUENCE function.
And then just sending it into the ROMAN function to make it mildly interesting.
I choose any cell in that array. Data, From Sheet.
In other words, it's coming from something in the sheet as opposed to an external workbook.
I have no problem with that name. That's a good name - From Sheet.
And it will recognize the extent of the array. How is this working behind the scenes?
Over here in the Applied Steps, click on Source and it is pulling it in from a Name called from Array underscore 1.
They created that on the fly for us. Pretty cool.
Close and Load. And we get our data.
Let's go back to the original and add more columns.
By 3. Something like that right?
And then come to the query results. Right-click and refresh.
And it picks up the larger array. Now how's it doing this?
Back here on the formulas tab, if we go into the name manager, it's actually secretly creating an array called from Array_1 that points to D6 hash.
So it's just creating a named range on the fly. Pretty cool.
Finally, dynamic arrays and power query - two of the great things in Excel that would never work together are now working together.
Of course, this is officially the first feature that makes this book obsolete. But that's OK. I'm glad for progress.
Check out MrExcel 2021 Unmasking Excel. Click the I in the top right hand corner.
If you like these videos please Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. See you next time for another netcast MrExcel.
Welcome back to MrExcel netcast. I am Bill Jelen. Something exciting today.
Look on the Data tab in Insiders Fast.
The old “From Table or Range” has now been named “From Sheet”.
Create a new query from the selected table, named range, or array in this workbook.
It used to be arrays wouldn't work with power query.
Great news, alright – so they've now added support for. Arrays. I have an array here using the SEQUENCE function.
And then just sending it into the ROMAN function to make it mildly interesting.
I choose any cell in that array. Data, From Sheet.
In other words, it's coming from something in the sheet as opposed to an external workbook.
I have no problem with that name. That's a good name - From Sheet.
And it will recognize the extent of the array. How is this working behind the scenes?
Over here in the Applied Steps, click on Source and it is pulling it in from a Name called from Array underscore 1.
They created that on the fly for us. Pretty cool.
Close and Load. And we get our data.
Let's go back to the original and add more columns.
By 3. Something like that right?
And then come to the query results. Right-click and refresh.
And it picks up the larger array. Now how's it doing this?
Back here on the formulas tab, if we go into the name manager, it's actually secretly creating an array called from Array_1 that points to D6 hash.
So it's just creating a named range on the fly. Pretty cool.
Finally, dynamic arrays and power query - two of the great things in Excel that would never work together are now working together.
Of course, this is officially the first feature that makes this book obsolete. But that's OK. I'm glad for progress.
Check out MrExcel 2021 Unmasking Excel. Click the I in the top right hand corner.
If you like these videos please Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. See you next time for another netcast MrExcel.