PostgreSQL database
Power BI is a powerful data visualization tool that allows users to create visually stunning reports and dashboards. One of the key features of Power BI is its ability to connect to a variety of data sources. One such data source is the PostgreSQL database. In this article, we will explore how to connect to a PostgreSQL database data source from inside Power BI using Power Query M Language code.
What is Power Query M Language?
Power Query M Language is a functional programming language used to perform data transformations in Power Query. Power Query is a data retrieval and transformation tool that is used to import data from various sources, clean and transform the data, and load it into Power BI for analysis. Power Query M Language is used to create custom functions and perform complex data transformations within Power Query.
Connecting to PostgreSQL database from Power BI
To connect to a PostgreSQL database from Power BI, we need to follow the steps below:
1. Open Power BI Desktop and click on the “Get Data” button on the Home tab.
2. In the “Get Data” window, select “PostgreSQL” from the list of available data sources.
3. In the “PostgreSQL database” window, enter the server name, database name, and authentication details. You can also choose to import data using a query or load the entire table.
4. Click on the “Connect” button to establish a connection to the PostgreSQL database.
Using Power Query M Language to Connect to PostgreSQL database
The steps above are the basic steps to connect to a PostgreSQL database from Power BI. However, if you want to customize the connection or perform more complex data transformations, you can use Power Query M Language.
To use Power Query M Language to connect to a PostgreSQL database, follow the steps below:
1. Open Power BI Desktop and click on “Edit Queries” from the Home tab.
2. In the “Queries” pane, click on “New Source” and select “Blank Query”.
3. In the “Query Editor”, click on “Advanced Editor” from the “View” tab.
4. In the “Advanced Editor”, enter the following code to establish a connection to the PostgreSQL database:
let
Source = PostgreSQL.Database(“ServerName”, “DatabaseName”, [Query=”select from TableName”]),
#”TableName” = Source{[Schema=”public”,Item=”TableName”]}[Data]
in
#”TableName”
Replace “ServerName”, “DatabaseName”, and “TableName” with the appropriate values for your database. This code will import all the data from the “TableName” table in the PostgreSQL database.
5. Click on the “Done” button to save the changes.
Customizing the Connection
The code above imports all the data from the specified table in the PostgreSQL database. However, you can customize the connection by adding parameters to the code.
For example, you can add a parameter to specify the user name and password for the database connection:
let
Source = PostgreSQL.Database(“ServerName”, “DatabaseName”, [Query=”select from TableName”, Username=”UserName”, Password=”Password”]),
#”TableName” = Source{[Schema=”public”,Item=”TableName”]}[Data]
in
#”TableName”
Replace “UserName” and “Password” with the appropriate values for your database.
You can also add a parameter to specify the port number for the database connection:
let
Source = PostgreSQL.Database(“ServerName”, “DatabaseName”, [Query=”select from TableName”, Username=”UserName”, Password=”Password”, Port=5432]),
#”TableName” = Source{[Schema=”public”,Item=”TableName”]}[Data]
in
#”TableName”
Replace “5432” with the appropriate port number for your database.
Conclusion
In conclusion, connecting to a PostgreSQL database data source from inside Power BI using Power Query M Language code is a powerful feature that allows users to customize their connection and perform complex data transformations. By following the steps outlined in this article, users can easily connect to a PostgreSQL database and import data into Power BI for analysis.
