Skip to content
Mike Evans-Larah By Mike Evans-Larah Software Engineer III
Power Query - Where can you use it? - Power BI

What is Power Query?

Power Query is data processing / transformation engine capable of performing a wide variety of ETL tasks and allowing users to "mash-up" data across sources. It has a graphical interface for building up the queries and transformations. Under the covers, it uses the Power Query M formula language for expressing its queries, which can be edited directly for advanced queries. There are hundreds of connectors that can used for reading data across different sources.

The engine began life as part of the Power Pivot extension for Excel, used for getting and transforming data to use in Pivot Tables.

Since then, it has been integrated across many more applications in the Microsoft ecosystem, and we'll take a look at them in this series of posts. In this post, we look at Power BI integrations.

Power BI

There are a few different ways of using Power Query from within Power BI.

Power BI Desktop

Firstly, you can use Power BI Desktop to utilize Power Query.

Similar to Excel, you can start by either using the 'Get Data' button or select one of the common data sources to use a wizard interface to connect to a data source, or use 'Transform Data' to open the Power Query Editor directly.

Screenshot of 'Get Data' pop-up in Power BI Desktop

Once you have finished building your query, the data is loaded into the report's model and can be used for building visualizations.

Screenshot of an example visualization in Power BI Desktop

How data is refreshed in Power BI depends on the storage modes for the datasets used in the report. Each dataset can use one of the following storage modes:

  • Import
  • DirectQuery
  • LiveConnect
  • Push

Only datasets using Import mode require refreshing; the DirectQuery and LiveConnect query the underlying datasets on every user interaction, and Push mode requires data to be pushed into the dataset externally.

For Import mode datasets, refresh can either be done manually (for example, using the 'Refresh' button in Power BI Desktop) or, once the report is published to a Power BI workspace, refresh can be scheduled to run on a regular basis.

Power BI Dataflows

In Power BI online, you can create dataflows, which are reusable units of transformation logic powered by Power Query. You need either a Pro or PPU (Premium Per User) license to create dataflows, or to be using a Premium capacity workspace.

Discover your Power BI Maturity Score by taking our FREE 5 minute quiz.

To create a dataflow, in the PBI workspace, select 'Dataflow' under the + New menu: Screenshot of option to create a new dataflow in a Power BI online workspace

Then the basic option is to create a new table in your dataflow. This will take you to page where you can select your data source, either connecting to an online source or uploading a file:

Screenshot of page for selecting a data source for a dataflow table in Power BI online

Once you have selected your data source and configured any necessary credentials for connecting to it, you will be able to edit the transformation using the familiar Power Query editor.

Screenshot of Power Query editor for a dataflow in Power BI online

Once you have saved the transformation, the table will be available in the dataflow. You will be offered an opportunity to either refresh the data now or set up a refresh schedule for the dataflow.

Screenshot of dataflow refresh options in Power BI online

If using a refresh schedule, it supports up to 8 refreshes of the data per day:

Screenshot of setting up scheduled refresh for a dataflow table in Power BI online

There is also the option of using incremental refresh for tables in the dataflow. This allows you to process only a subset of the data based on a DateTime column in your table, selecting the period in which to store data and the period in which to refresh data. This can be beneficial for large datasets to reduce refresh time, increase reliability and reduce resource consumption

Screenshot of setting up incremental refresh for a dataflow table in Power BI online

To consume the tables in the dataflow, you can connect to the dataflow when building reports in Power BI Desktop:

Screenshot of connecting to dataflows from Power BI Desktop

Power BI Datamarts

The third option for using Power Query in Power BI is through Datamarts.

Power BI Datamarts use Power Query in a near identical way to as described above for Power BI Dataflows. Creating a Datamart requires a Premium workspace, but other than that the Power Query features and processes are the same.

Mike Evans-Larah

Software Engineer III

Mike Evans-Larah

Mike is a Software Engineer at endjin with over a decade of experience in solving business problems with technology. He has worked on a wide range of projects for clients across industries such as financial services, recruitment, and retail, with a strong focus on Azure technologies.