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 & Connectionsgroups. Power Query Books Gil Raviv Gil Raviv, Collect, Combine and Transform Data Using Power Query in Excel and Power BI (Pearson Education, 2019).
- Microsoft Power Query For Excel 2013 Download
- Microsoft Power Query For Excel
- Microsoft Power Query For Excel
Another way to run queries is to use Microsoft Power Query (also known in Excel 2016 and up as Get and Transform). The AddIn provided by Microsoft does require knowledge of the SQL Language, rather allowing you to click your way through the data you want to tranform. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query Edit. For more information see Create, load, or edit a query in Excel. Select Home Append Queries. The default action is to do an inline append.
Power Query offers several ways to create and load Power queries into your workbook. You can also set default query load settings in the Query Options window. Tip To tell if data in a worksheet is shaped by Power Query, select a cell of data, and if the Query context ribbon tab appears, then the data was loaded from Power Query.
Summary
| Item | Description |
|---|---|
| Release State | General Availability |
| Products | Power BI (Datasets) Power BI (Dataflows) Power Apps (Dataflows) Excel Dynamics 365 Customer Insights Analysis Services |
| Authentication Types Supported | Database (Username/Password) |
| Function Reference Documentation | PostgreSQL.Database |
Microsoft Power Query For Excel 2013 Download
Note
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Prerequisites
As of the December 2019 release, NpgSQL 4.0.10 shipped with Power BI Desktop and no additional installation is required. GAC Installation overrides the version provided with Power BI Desktop, which will be the default. Refreshing is supported both through the cloud in the Power BI Service and also on premise through the Gateway. In the Power BI service, NpgSQL 4.0.10 will be used, while on premise refresh will use the local installation of NpgSQL, if available, and otherwise use NpgSQL 4.0.10.
For Power BI Desktop versions released before December 2019, you must install the NpgSQL provider on your local machine. To install the NpgSQL provider, go to the releases page and download the relevant release. The provider architecture (32-bit or 64-bit) needs to match the architecture of the product where you intend to use the connector. When installing, make sure that you select NpgSQL GAC Installation to ensure NpgSQL itself is added to your machine.
We recommend NpgSQL 4.0.10. NpgSQL 4.1 and up won't work due to .NET version incompatibilities.
Capabilities Supported
- Import
- DirectQuery (Power BI only, learn more)
- Advanced options
- Command timeout in minutes
- Native SQL statement
- Relationship columns
- Navigate using full hierarchy
Connect to a PostgreSQL database from Power Query Desktop
Once the matching Npgsql provider is installed, you can connect to a PostgreSQL database. To make the connection, take the following steps:
Select the PostgreSQL database option in the connector selection.
In the PostgreSQL database dialog that appears, provide the name of the server and database.
Select either the Import or DirectQuery data connectivity mode.
If this is the first time you're connecting to this database, input your PostgreSQL credentials in the User name and Password boxes of the Database authentication type. Select the level to apply the authentication settings to. Then select Connect.
For more information about using authentication methods, go to Authentication with a data source.
Note
If the connection is not encrypted, you'll be prompted with the following message.
Select OK to connect to the database by using an unencrypted connection, or follow the instructions in Enable encrypted connections to the Database Engine to set up encrypted connections to PostgreSQL database.
In Navigator, select the database information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.
Connect to a PostgreSQL database from Power Query Online
Mac os x lion upgrade. To make the connection, take the following steps:
Select the PostgreSQL database option in the connector selection.
In the PostgreSQL database dialog that appears, provide the name of the server and database.
Select the name of the on-premises data gateway you want to use.
Macos mojave pc. Select the Basic authentication kind and input your MySQL credentials in the Username and Password boxes.
If your connection isn't encrypted, clear Use Encrypted Connection.
Select Next to connect to the database.
In Navigator, select the data you require, then select Transform data to transform the data in Power Query Editor.
Connect using advanced options
Power Query Desktop provides a set of advanced options that you can add to your query if needed.
The following table lists all of the advanced options you can set in Power Query Desktop.
| Advanced option | Description |
|---|---|
| Command timeout in minutes | If your connection lasts longer than 10 minutes (the default timeout), you can enter another value in minutes to keep the connection open longer. This option is only available in Power Query Desktop. |
| SQL statement | For information, go to Import data from a database using native database query. |
| Include relationship columns | If checked, includes columns that might have relationships to other tables. If this box is cleared, you won’t see those columns. |
| Navigate using full hierarchy | If checked, the navigator displays the complete hierarchy of tables in the database you're connecting to. If cleared, the navigator displays only the tables whose columns and rows contain data. |
Microsoft Power Query For Excel
Once you've selected the advanced options you require, select OK in Power Query Desktop to connect to your PostgreSQL database.
Native query folding
By default, native query folding is enabled. Operations that are capable of folding will be applied on top of your native query according to normal Import or Direct Query logic. Native Query folding isn't applicable with optional parameters present in Value.NativeQuery().
In the rare case that folding doesn't work with native query folding enabled, you can disable it. To disable native query folding, set the EnableFolding flag to false for Value.NativeQuery() in the advanced editor.
Microsoft Power Query For Excel
Sample:Value.NativeQuery(target as any, query, null, [EnableFolding=false])
Troubleshooting
Your native query may throw the following error:
We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option.
A basic trouble shooting step is to check if the query in Value.NativeQuery() throws the same error with a limit 1 clause around it:
select * from (query) _ limit 1
Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with Analysis Services, Excel, and Power BI workbooks. A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.
Get started
Quickstart
Concept
Learn
Functions
Reference
Overview
Specification
Architecture