Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Prerequisites
Since December 2019, Power BI Desktop ships with NpgSQL, and no other installation is required. As of the October 2024 release, NpgSQL 4.0.17 is the version included. GAC Installation overrides the version provided with Power BI Desktop, which is the default. Refreshing is supported both through the cloud in the Power BI service and also on premise through the on-premises data gateway. To refresh data from the Power BI service without an on-premises data gateway, PostgreSQL must be hosted in a manner that allows direct connection from the Power BI services on Azure. This connectivity is natively supported for PostgreSQL hosted in Microsoft Azure. For other hosting environments, consult your hosting provider about configuring your PostgreSQL for direct access from the internet. If PostgreSQL is configured so that it can't be directly accessed from the internet (recommended for security), you need to use an on-premises data gateway for refreshes. In the Power BI service, NpgSQL 4.0.17 is used, while on premises refresh uses the local installation of NpgSQL, if available, and otherwise uses NpgSQL 4.0.17.
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, search for the latest v4.0.x version, and download and run the .msi file. 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.17. NpgSQL 4.1 and up won't work due to .NET version incompatibilities.
For Power Apps, you must install the NpgSQL provider on your local machine. To install the NpgSQL provider, go to the releases page and download the relevant version. Download and run the installer (the NpgSQL-[version number].msi) file. Ensure you select the NpgSQL GAC Installation and on completion restart your machine for this installation to take effect.
Capabilities Supported
Import
DirectQuery (Power BI semantic models)
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. For more information, go to Where to get data.
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 you're connecting to this database for the first time, 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.
If the connection isn't encrypted, you're 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
To make the connection, take the following steps:
Select the PostgreSQL database option in the connector selection. For more information, go to Where to get data.
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.
Select the Basic authentication kind and input your PostgreSQL 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 time-out), you can enter another value in minutes to keep the connection open longer. This option is only available in Power Query Desktop.
If checked, includes columns that might have relationships to other tables. If this box is cleared, those columns aren't displayed.
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.
Once you select 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 are 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.
Sample:
Value.NativeQuery(target as any, query, null, [EnableFolding=false])
Troubleshooting
Your native query might 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 troubleshooting step is to check if the query in Value.NativeQuery throws the same error with a limit 1 clause around it:
Azure Database for PostgreSQL is a Platform as a Service database service in the Microsoft cloud. It bases itself on the PostgreSQL open-source relational database and includes built-in high availability, automatic backup and restore, as well as comprehensive security features. The pay-as-you-go pricing model provides predictable performance and near-instant scaling. In this learning path, you're introduced to the main features of PostgreSQL and how they work in Azure Database for PostgreSQL. You learn abou