How Power BI Works: Power Query, the Data Model and DAX

Power BI is Microsoft’s business analytics platform. Development began in 2010 and the first version was released at the end of 2013. New versions come out every month. Its closest competitors are Tableau and Qlik.

The system is made up of two distinct parts, and it is worth keeping them apart because they do different jobs: Power Query prepares the data, Power BI presents it.

Power Query: preparing the data

Power Query is where the invisible but most important work happens: the data is gathered and put in order.

You can import not only from the usual sources (SQL databases, Excel, CSV and text files) but also from PDF, XML, JSON, Dynamics or Salesforce. The list of sources grows with every update. There are less common options too: data can be pulled straight from web pages or from Google Analytics.

Particularly useful in practice is the ability to collect data from every file in a single folder. If you receive an Excel file each month, the whole folder can be turned into a single table that updates itself whenever a new file arrives.

At the preparation stage you can also clean up the data: make bulk changes, add or remove columns, or split one column into several.

Why the step history matters

As you build a data source, Power Query records every action as a separate step. Any of them can later be adjusted or deleted, and new ones can be inserted.

This is not a technical detail. It is precisely what makes reports reproducible: when, six months later, someone asks why a measure is calculated the way it is, the answer is there in the list of steps rather than disappearing along with the person who built the report.

The data model: where the analysis happens

Power Query prepares one or more tables. Power BI links them through logical relationships into a data model.

This step is what separates analytics from a spreadsheet in Excel. Once the sales table is linked to the customer, product and period tables, any measure can be sliced any way you like, by customer, product group, department or month, without building a new report.

How you work with it

Both Power Query and Power BI can be used in two ways: drag-and-drop, working with the mouse alone, or by writing formulas.

Power Query uses the “M” language, Power BI uses DAX (Data Analysis Expressions). Each has more than a thousand functions, so practically any calculation, however complex, can be programmed.

In practice this means users can build a simple report themselves, while more complex logic, such as calculating margin with cost of sales allocation or comparing against the same period last year, is best left to someone who writes DAX every day.

Visualisation

Power BI works with the data prepared by Power Query and displays it visually: as tables, charts and filters. Formatting options let you highlight values based on criteria, for example marking a variance from budget in red.

An important feature is drill-down: from a summary figure you can go all the way down to a specific general ledger account without leaving the report.

What this means for your company

This whole structure explains why business analytics with Power BI works without manual effort: Power Query fetches the data on a schedule, the model recalculates the measures and the report refreshes itself.

You do not need to learn either “M” or DAX. The ready-built report packages for Rivilė and Finvalda users already include both the data model and the calculations; all that remains is to connect them to your database. Prices are on the pricing page.