Academy
Data Lake

Practical guide: connect Power BI to the Data Lake

This guide shows the full path to bring the results of your analyses into Power BI through the Data Lake: the connection, filtering by the reference date with a dynamic parameter, and DirectQuery mode for real-time dashboards. To understand the feature — activation and how the history is stacked — see the article "Data Lake: connect your BI or spreadsheet".

Before you start

  • Enable the desired tables in the process's Data Lake tab and leave the screen open — the connection details (server, database, user, and password) will be copied from there.

  • Remember that the tables load the process's full history, with each execution identified by the reference date column — it appears when you view the table and is the key for the filter we will build.

Step 1 — Connect

  1. In Power BI Desktop, go to Home > Get data and choose PostgreSQL database.

  2. Paste the Server and Database copied from the Data Lake screen.

  3. Choose the connectivity mode:

  • Import: Power BI copies the data into the report and refreshes it on the schedule.

  • DirectQuery: each visual queries the Data Lake in real time — the choice for dynamic dashboards.

  1. In the credentials step, select the Database tab and enter the user and password copied from the Data Lake screen.

  2. In the Navigator, select the published tables (by Table Name) and click Transform Data — we will prepare the filter before loading.

Step 2 — Create the date parameter

  1. In the Power Query Editor, go to Home > Manage Parameters > New Parameter.

  2. Create a parameter with a descriptive name (for example, DataReferencia), type Date (or Text, if you prefer the column's exact format), and a current value (for example, the latest closing date).

Step 3 — Apply the parameter in the filter

The recommended approach is to filter through the Power Query interface — Power BI converts the filter into a WHERE clause sent to the Data Lake (query folding), which works in both Import and DirectQuery modes:

  1. In the table query, click the arrow in the header of the reference date column and choose Date Filters > Is Equal To.

  2. In the filter dialog, replace the fixed value with the parameter: click the icon to the left of the value field and select Parameter > DataReferencia.

  3. Confirm and click Close & Apply.

From there, changing the dashboard period means editing a single value in Home > Transform data > Edit Parameters, without touching the query.

For advanced users, the alternative is a native SQL statement in the connection, injecting the parameter into the WHERE clause. Prefer filtering through the interface: it preserves query folding and avoids manual SQL maintenance.

Step 4 — Publish and maintain

  • In Power BI Service, the parameter value can be changed in the dataset settings (Parameters section), without republishing the report.

  • To put the choice of period in the hands of the report consumers, dynamic M parameters allow you to bind the parameter to a slicer (see the Power BI documentation for the feature requirements).

DirectQuery: real-time data

In DirectQuery mode, each visual queries the Data Lake at the moment it is viewed — the dashboard reflects every new run of the platform without relying on scheduled refresh. It is the ideal combination with the reference-date filter: the query sent to the database arrives already filtered, lightweight, and always current.

Warning — high volumes: the Data Lake preserves the process's entire history and is not recommended for very high volumes. Without the reference-date filter, each refresh or visual can scan the entire history — keep the filter in the query (not just in the visual) so that only the necessary period is transferred.

  • Data Lake: connect your BI or spreadsheet

  • Introduction to integrations

  • Database Export step

Was this helpful?