Azure Database for PostgreSQL
Power Query is a powerful data connection tool that is integrated into Microsoft Power BI. It allows users to connect to different data sources that Power BI supports, including Azure Database for PostgreSQL. In this article, we will explore the Power Query M language code for connecting to the Azure Database for PostgreSQL data source from inside Power BI.
What is Power Query M Language?
Power Query M language is a functional programming language used to create queries to extract, transform and load data. It is a case-sensitive language that can be used to create custom functions, define variables and create complex queries. Power Query M language is used to create queries in Power BI, Power Query and Excel.
What is Azure Database for PostgreSQL?
Azure Database for PostgreSQL is a fully managed database service provided by Microsoft Azure. It is a relational database that is based on the open-source PostgreSQL database engine. Azure Database for PostgreSQL provides enterprise-grade performance, security, and scalability for your applications.
Connecting to Azure Database for PostgreSQL from Power BI
To connect to the Azure Database for PostgreSQL data source from inside Power BI, you need to create a new data source using the Power Query Editor. Here are the steps to create a new data source:
1. Open Power BI Desktop and click on “Get Data”.
2. Choose “Azure” under the “Database” category and select “Azure Database for PostgreSQL“.
3. Enter the server name, database name, and login information for your Azure Database for PostgreSQL.
4. Click “Connect” and wait for Power BI to establish the connection.
Writing Power Query M Language Code for Azure Database for PostgreSQL
Once you have established the connection to your Azure Database for PostgreSQL data source, you can start writing Power Query M language code to extract, transform and load data. Power Query M language code is written in the Power Query Editor using the M formula language.
Here is an example of Power Query M language code for connecting to Azure Database for PostgreSQL:
let
Source = PostgreSQL.Database(“postgres.database.azure.com”, “mydatabase”, [Query=”select from mytable”]),
mytable = Source{[Schema=”public”, Item=”mytable”]}[Data]
in
mytable
In the above example, we use the PostgreSQL.Database function to establish a connection to our Azure Database for PostgreSQL. We then use the Query parameter to specify the SQL query that we want to run against our data source. Finally, we use the { } brackets to reference the data that we want to extract from our data source.
Conclusion
In summary, Power Query M language provides a flexible and powerful way to connect to the Azure Database for PostgreSQL data source from inside Power BI. With the right code, you can extract, transform and load data from your Azure Database for PostgreSQL into Power BI, allowing you to create insightful reports and dashboards that can help you make better business decisions. By following the steps outlined in this article, you can connect to your Azure Database for PostgreSQL data source and start writing Power Query M language code today.
