10 tips and tricks for using Power Query in Power BI – Part 1

January 10, 2023 Alex L

Disclaimer:

We apologize for the length of this post. We originally intended to provide 10 tips and tricks in one easy-to-read article, but our enthusiasm for the subject got the better of us. The first 5 tips and tricks turned out to be so epic that we had to split the post into two. We promise that the second part will be just as informative and entertaining as the first, and we hope you won't hold our wordiness against us. We're just really passionate about this topic, and we hope you are too!

As the amount of data that businesses generate and store continues to grow, the need for efficient data preparation and analysis tools becomes increasingly important. One tool that has become essential for businesses of all sizes is Power BI, a business analytics service from Microsoft that provides interactive visualizations and business intelligence capabilities. Within Power BI, Power Query is a powerful tool that allows users to transform, clean, and shape data from a wide range of sources.

In this blog post, we will explore 10 tips and tricks for using Power Query in Power BI, along with examples of how they can benefit businesses. From data loading and filtering to advanced transformations and data modeling, these tips and tricks will help users to streamline their data preparation and analysis processes, leading to more accurate and informed decision-making.

Use Parameters

Use Parameters for Flexible Data Loading With parameters, you can create dynamic data loading processes that enable you to easily switch between different data sources, filters, or date ranges. For example, you can create a parameter that lets users choose a date range for a report, and then use that parameter to filter the data.

Power Query is a data transformation and preparation tool that is built into Power BI. It allows users to connect to various data sources, transform and shape the data, and load it into a data model for reporting and analysis. One of the key features of Power Query is its ability to create flexible and reusable data transformation workflows. Parameters are an essential tool for achieving this flexibility.

A parameter is a user-defined value that can be used in Power Query formulas and transformations. Parameters allow users to define values that can be changed at runtime, which can be incredibly useful for building flexible and interactive reports. Parameters can be used to:

Dynamically filter data based on user input
Control the granularity of time-based calculations
Standardize column names across multiple data sources
Set dynamic thresholds for data quality checks
And more!

Using parameters in Power Query can save time and increase productivity, as it enables users to quickly and easily modify the data transformation workflow without having to rewrite complex formulas or transformations. Additionally, parameters can be shared across multiple queries, making it easy to standardize and reuse data transformations across an entire report or data model.

In summary, using parameters is a powerful tip and trick for Power Query, as it allows users to create highly flexible and reusable data transformation workflows that can save time and increase productivity. By leveraging the power of parameters, users can create dynamic and interactive reports that can adapt to changing business needs.

Two illustrated examples on how to use parameters for flexible data loading in Power Query:

Example 1: Flexible Date Range Filter

Suppose you have a dataset that contains sales data for the past year, and you want to be able to filter the data based on a flexible date range. To accomplish this, you can use a parameter to define the start and end dates of the date range.

Create a parameter: In the Power Query Editor, go to the Home tab, and click on the "Manage Parameters" button. In the "Manage Parameters" dialog box, click "New Parameter" and name it "Date Range".
Set the data type: In the "New Parameter" dialog box, set the data type to "DateTime".
Define the default values: In the "New Parameter" dialog box, define the default values for the start and end dates of the date range.
Use the parameter in a filter: In the Power Query Editor, select the column containing the date values, and click on the "Filter Rows" button. In the "Filter Rows" dialog box, select "Between" as the filter type, and use the parameter as the filter values.

Example 2: Dynamic Source File Path

Suppose you have a set of CSV files that are updated on a regular basis, and you want to be able to load the most recent file each time you refresh the data. To accomplish this, you can use a parameter to define the file path.

Create a parameter: In the Power Query Editor, go to the Home tab, and click on the "Manage Parameters" button. In the "Manage Parameters" dialog box, click "New Parameter" and name it "File Path".
Set the data type: In the "New Parameter" dialog box, set the data type to "Text".
Define the default value: In the "New Parameter" dialog box, define the default value as the file path to the most recent CSV file.
Use the parameter in a data source: In the Power Query Editor, click on the "From File" button, and select "CSV" as the file type. In the "From CSV" dialog box, use the parameter as the file path.

By using parameters in Power Query, you can create flexible data loading processes that can adapt to changes in your data sources. These examples demonstrate just a few of the ways that parameters can be used to streamline your data preparation and analysis processes.

Bonus:

how to use parameters in Power Query that might "wow" your colleagues:

Example: Dynamic Filtering Based on User Input

Suppose you have a Power BI report that contains a table of sales data, and you want to allow users to filter the data based on any column in the table. You could accomplish this using parameters in Power Query.

Create a parameter: In the Power Query Editor, go to the Home tab, and click on the "Manage Parameters" button. In the "Manage Parameters" dialog box, click "New Parameter" and name it "Filter Column".
Set the data type: In the "New Parameter" dialog box, set the data type to "Text".
Define the default value: In the "New Parameter" dialog box, define the default value as the name of the column that you want to filter by.
Use the parameter in a filter: In the Power Query Editor, select the table containing the sales data, and click on the "Filter Rows" button. In the "Filter Rows" dialog box, select "Custom" as the filter type, and enter the following formula:

= [#"& Filter Column &" ] > 0

This formula uses the parameter to dynamically filter the table based on the column specified by the user. The "&" symbol is used to concatenate the parameter value with the rest of the formula.

Add a parameter prompt to the report: In the Power BI Desktop, go to the "View" tab, and click on the "Parameter" button. In the "New Parameter" dialog box, select the "Filter Column" parameter, and check the "Required" option. This will prompt the user to enter a value for the parameter when they view the report.

Now, when a user views the report, they will be prompted to enter the name of the column they want to filter by. Once they enter a value, the report will dynamically filter the table based on their input. This provides a highly customizable and interactive experience for the user, without requiring any additional development effort. Your colleagues may be impressed by the flexibility and efficiency of this parameter-based solution.

Combine multiple Data Source

Combine Multiple Data Sources with Merge Queries Merge queries allow you to combine data from multiple sources into a single table. For example, you can merge customer data from a CRM system with sales data from an ERP system to create a complete view of customer activity.

Power Query provides several options for combining data from multiple sources, including joining tables, appending tables, and merging queries. The Merge Queries feature is a powerful tool that allows users to combine data from multiple tables or queries into a single result set. This feature is particularly useful when working with data that is stored in multiple tables, files, or databases.

Here are some scenarios where you might use Merge Queries to combine data from multiple sources:

Combining data from multiple tables: If you have multiple tables in a database that contain related information, you can use Merge Queries to combine the data into a single table. For example, if you have a table of customer data and a table of order data, you could use Merge Queries to combine the data into a single table that shows each customer's order history.
Merging data from different sources: If you have data stored in different databases or files, you can use Merge Queries to combine the data into a single table. For example, if you have a database that contains customer information and a CSV file that contains order information, you could use Merge Queries to combine the data into a single table.
Combining data from different granularities: If you have data at different levels of granularity, such as daily sales data and monthly sales data, you can use Merge Queries to combine the data into a single table. For example, you could use Merge Queries to combine daily sales data with monthly sales data to create a report that shows both levels of detail.

Here's how to use Merge Queries to combine data from multiple sources:

Connect to the data sources: In Power Query, go to the Home tab, and click on the "Combine Queries" button. In the drop-down menu, select "Merge Queries".
Select the tables or queries to merge: In the "Merge Queries" dialog box, select the first table or query you want to merge. Then, select the second table or query you want to merge.
Choose the join type: In the "Merge Queries" dialog box, choose the join type you want to use. For example, you could use an inner join to only include rows where there is a match in both tables, or an outer join to include all rows from both tables.
Choose the join columns: In the "Merge Queries" dialog box, choose the columns that you want to use to join the tables. The columns should contain matching values in both tables.
Expand the resulting table: In the Power Query Editor, select the merged query and click on the "Expand" button to see the columns from both tables in a single table.

Use merge Queries – A great function for accountant and the finance teams

For accounting and finance teams, the ability to quickly and easily add a new month of transactional data to build YTD financial reporting is critical. Using Power Query's Merge Queries function, accounting and finance teams can easily combine data from multiple sources, including spreadsheets, databases, and other data sources, to create a comprehensive view of their financial data.

Here's an example of how accounting and finance teams can use the Merge Queries function to add a new month of transactional data to build YTD financial reporting:

Connect to the data sources: In Power Query, go to the Home tab, and click on the "Combine Queries" button. In the drop-down menu, select "Merge Queries".
Select the tables or queries to merge: In the "Merge Queries" dialog box, select the existing YTD financial data table, and then select the new month of transactional data.
Choose the join type: In the "Merge Queries" dialog box, choose the join type you want to use. For example, you could use an inner join to only include rows where there is a match in both tables, or an outer join to include all rows from both tables.
Choose the join columns: In the "Merge Queries" dialog box, choose the columns that you want to use to join the tables. The columns should contain matching values in both tables.
Expand the resulting table: In the Power Query Editor, select the merged query and click on the "Expand" button to see the columns from both tables in a single table.
Transform and load the data: Once the data is merged and the columns are expanded, accounting and finance teams can use the Power Query Editor to transform and load the data into a YTD financial reporting dashboard or spreadsheet.

By using the Merge Queries function in Power Query, accounting and finance teams can easily add a new month of transactional data to their YTD financial reporting without the need for manual data entry or complex data manipulation. This not only saves time, but also ensures that the YTD financial reporting is accurate and up-to-date.

In summary, using Merge Queries in Power Query is a powerful tip and trick that allows users to combine data from multiple sources into a single result set. This feature is particularly useful when working with data that is stored in multiple tables, files, or databases. By combining data using Merge Queries, users can create more comprehensive reports and analyses that provide a more complete view of their data.

Transform Data with Custom Function

Transform Data with Custom Functions, Custom functions allow you to create reusable code snippets for common data transformations. For example, you can create a custom function that extracts the year from a date, and then use that function in multiple queries.

Custom functions allow you to create reusable code snippets for common data transformations that you can use across multiple queries. They are created in the Power Query Editor using the M language, which is the language used by Power Query to perform data transformations.

By creating custom functions, you can simplify complex data transformations and make them reusable across multiple queries. For example, if you frequently need to calculate the average of a specific column, you can create a custom function that calculates the average and use it in multiple queries. This saves time and ensures consistency across your data transformations.

Here's an example of how to create a custom function in Power Query:

Open the Power Query Editor and go to the "New Source" dropdown menu. Select "Blank Query" and give your query a name.
In the formula bar, write your function using the M language. For example, to create a function that calculates the average of a specific column, you can write the following code:

let

average = (column) => List.Average(column)

average

Click "Done" to save your function.

To use your custom function, create a new query and go to the "Add Column" tab. Select "Invoke Custom Function" and select your custom function from the list.
In the "Invoke Custom Function" dialog box, select the column you want to apply the function to, and click "OK".
The new column with the result of the custom function will be added to your query.

Custom functions in Power Query can be extremely powerful and time-saving, especially for complex data transformations that need to be performed across multiple queries. By creating custom functions, you can make your data transformation process more efficient and consistent.

A second example you might use:

let

extractFirstLetters = (text as text) => Text.Combine(List.Transform(Text.Split(text, " "), each Text.Start(_, 1)), " ")

extractFirstLetters

This function takes a string as input, splits it into words, extracts the first letter of each word, and combines them back into a string.

You can use this function in a new query to extract the first letters of a string column. Here's how:

Create a new query and connect to your data source.
In the query editor, go to the "Add Column" tab and select "Custom Column".
In the "Custom Column" dialog box, enter a name for your new column, and enter the following formula:

extractFirstLetters([Column])

Replace [Column] with the name of the column that you want to extract the first letters from.

Click "OK" to create the new column.
The new column will be added to your query, with the first letters of each word in the original column.

You can now use this custom function in any query that requires the extraction of the first letters of each word in a string column. This can save you time and make your data transformation process more efficient.

Filter and Sort Data with Query Dependencies

Query dependencies allow you to filter and sort data in a more efficient way by applying the filter or sort operation in a previous query step. For example, you can filter a dataset by a certain value in one query, and then sort the filtered data in a subsequent step.

Query dependencies are an important feature of Power Query that allow users to manage the relationships between queries in their data model. By understanding and using query dependencies effectively, Power BI users can optimize their data transformation process, improve performance, and create more robust data models.

In Power Query, each query is a self-contained unit that performs a specific data transformation. However, it is often necessary to combine multiple queries to create a more complete data model. This is where query dependencies come in. By setting up relationships between queries, Power BI users can ensure that changes to one query are automatically propagated to other queries that depend on it. This can save time and effort, and ensure that the data model remains consistent and up-to-date.

There are several benefits to using query dependencies in Power Query:

Improved performance: By setting up query dependencies, Power BI users can optimize their data transformation process and improve performance. Queries that depend on other queries can be updated automatically, reducing the need for manual updates and improving the speed of the data transformation process.
Increased efficiency: Query dependencies can also improve the efficiency of the data transformation process. By setting up relationships between queries, Power BI users can avoid duplicating work and ensure that changes to one query are automatically propagated to other queries that depend on it.
Robust data models: Query dependencies can also help to create more robust data models. By ensuring that queries are updated automatically, Power BI users can avoid errors and inconsistencies in their data model, which can lead to inaccurate results and a loss of credibility.

To use query dependencies effectively, Power BI users should follow these best practices:

Understand the relationships between queries: Before setting up query dependencies, it is important to understand the relationships between the queries in the data model. This will ensure that the dependencies are set up correctly and that changes to one query are propagated to other queries as intended.
Use naming conventions: To make it easier to understand the relationships between queries, it is a good idea to use naming conventions that reflect the dependencies between queries. For example, if Query B depends on Query A, you could name Query A "Source Data" and Query B "Transformed Data - Source Data".
Keep queries modular: To make it easier to manage query dependencies, it is a good idea to keep queries modular and self-contained. This will make it easier to update and modify individual queries without affecting other queries that depend on them.

Let's say you have two queries in your data model: "Sales Data" and "Product Data". The "Sales Data" query contains information about sales transactions, while the "Product Data" query contains information about the products being sold.

To create a more complete data model, you want to combine the two queries so that you can analyze sales data by product. To do this, you create a third query called "Sales by Product", which combines the "Sales Data" and "Product Data" queries.

Now, let's say you make changes to the "Product Data" query, such as adding a new product. Without query dependencies, you would need to update the "Sales by Product" query manually to include the new product.

However, by setting up a query dependency between the "Product Data" query and the "Sales by Product" query, any changes made to the "Product Data" query will be automatically propagated to the "Sales by Product" query. This ensures that the "Sales by Product" query remains up-to-date and accurate, without the need for manual updates.

In this example, query dependencies allow you to create a more complete and robust data model, and ensure that changes to the underlying data are automatically reflected in your analysis.

Combine data or use a relation in the data model?

Whether to use query dependencies or create relationships between tables in your data model depends on your specific use case and data requirements.

If you have a simple data model with a small number of tables and your data does not change frequently, creating relationships between tables is often the best approach. This allows you to easily filter and analyze your data using the familiar features of Power BI, such as slicers and visuals.

However, if you have a more complex data model with many tables and your data changes frequently, using query dependencies may be a better approach. This allows you to more easily manage and update your queries, and ensures that any changes to your underlying data are automatically reflected in your analysis.

In some cases, you may even want to use a combination of query dependencies and relationships to create a more flexible and robust data model.

Ultimately, the choice between using query dependencies or creating relationships between tables will depend on the specific requirements of your data model and analysis. It is important to carefully evaluate your data and choose the approach that best meets your needs.

In summary, query dependencies are an important feature of Power Query that allow users to manage the relationships between queries in their data model. By understanding and using query dependencies effectively, Power BI users can optimize their data transformation process, improve performance, and create more robust data models

Unpivot Data for Better Analysis

Unpivoting data is a useful technique for transforming data from a wide format to a long format. This makes it easier to analyze the data and create visualizations. For example, you can unpivot a table of sales data with columns for each month, and transform it into a table with separate rows for each month.

Unpivoting data is a useful technique for transforming data because it allows you to reshape your data so that it can be more easily analyzed and visualized in Power BI.

In a wide format, data is organized in columns, with each column representing a different variable. This can make it difficult to analyze and visualize the data, as you may need to manually select and filter multiple columns to create a complete picture of the data.

By contrast, in a long format, data is organized in rows, with each row representing a unique combination of variables. This can make it easier to analyze and visualize the data, as you can more easily filter and aggregate the data to create a more complete picture.

For example, let's say you have a table of sales data that contains columns for each month of the year. In the wide format, the table may look like this:

Product	Jan Sales	Feb Sales	Mar Sales
A	100	150	200
B	75	125	150
C	50	75	100

However, if you want to analyze sales data by month, you would need to manually select and filter multiple columns. By contrast, if you unpivot the data to a long format, the table would look like this:

Product	Month	Sales
A	January	100
A	February	150
A	March	200
B	January	75
B	February	125
B	March	150
C	January	50
C	February	75
C	March	100

In this long format, you can more easily filter and aggregate the data by month, allowing you to create more effective visuals and gain deeper insights into your data.

In summary, unpivoting data from a wide format to a long format is a useful technique for transforming data because it can make it easier to analyze and visualize the data. By organizing the data in rows instead of columns, you can more easily filter and aggregate the data to create a more complete picture of your data.

Leverage the Vertipaq engine for better performance!

Unpivoting data can actually improve Power BI performance, especially when using the VertiPaq engine. The VertiPaq engine is a powerful, in-memory data storage and processing technology used by Power BI to deliver fast query response times and efficient use of memory.

When data is unpivoted, it is transformed from a wide format to a long format. In a wide format, data is stored in columns, which can result in many redundant or null values. This can increase the size of the data set and make it more challenging for the VertiPaq engine to efficiently compress and store the data in memory.

By contrast, in a long format, data is stored in rows, which can help eliminate redundancy and null values. This can reduce the size of the data set and make it easier for the VertiPaq engine to compress and store the data in memory. Additionally, the long format can simplify the relationships between tables, allowing for faster query processing and improved performance.

For example, let's say you have a wide format table that contains sales data for different regions and months:

Region	Jan Sales	Feb Sales	Mar Sales
East	100	150	200
West	75	125	150
North	50	75	100

This table has many redundant and null values, as each row contains only three values out of twelve possible values. By unpivoting the table to a long format, we can eliminate the redundancy and null values:

Region	Month	Sales
East	January	100
East	February	150
East	March	200
West	January	75
West	February	125
West	March	150
North	January	50
North	February	75
North	March	100

This long format table can be more efficiently compressed and stored in memory by the VertiPaq engine, resulting in faster query response times and improved performance.

In summary, unpivoting data from a wide format to a long format can improve Power BI performance, especially when using the VertiPaq engine. By eliminating redundancy and null values and simplifying table relationships, the long format can help reduce the size of the data set and make it easier for the VertiPaq engine to compress and store the data in memory.

In conclusion, Power Query is a powerful tool that allows users to transform, clean, and shape data from a wide range of sources in Power BI. By implementing the 10 tips and tricks discussed in this blog post, businesses can streamline their data preparation and analysis processes, leading to more accurate and informed decision-making. With Power Query, users can easily combine, transform, and filter data from multiple sources, allowing them to generate insights that drive business success.

Business leaders who are able to leverage Power Query will have a significant advantage in today's data-driven business environment. By using Power Query, businesses can gain a deeper understanding of their data, identify trends and patterns, and make informed decisions based on solid data analysis. With the ability to easily combine and transform data from multiple sources, businesses can gain a more comprehensive view of their operations and customers, leading to improved efficiency and profitability.

Overall, Power Query is an essential tool for any business that wants to get the most out of their data. By mastering the tips and tricks discussed in this blog post, businesses can harness the power of Power Query to gain a competitive edge in their industry and make data-driven decisions that lead to success.

Back to blog

Item added to your cart