What is Power BI Query?
Power BI Query is a component of Microsoft Power BI, a business intelligence and data visualization tool. Power BI Query, or Power Query, is a data transformation and preparation tool used to connect to various data sources, transform and shape data, and load it into Power BI for analysis and reporting. Power Query is available in both Power BI Desktop and Excel, and it helps users clean, transform, and prepare data for analysis.
Table of contents
Key Takeaways
- Power Query is a data transformation and preparation tool in Power BI for connecting to various data sources, shaping data, and loading it into the Power BI model.
- You can perform data transformations, combine data from multiple sources, and create custom expressions using the M language.
- Proper data preparation is essential for accurate analysis and reporting in Power BI.
- Power Query supports query folding to optimize data retrieval from data sources.
- Learning to manage query steps and use Power Query effectively is key to leveraging its capabilities in Power BI.
How to use Power BI Query Editor?
To use the Power BI Query Editor, follow the steps highlighted below:
Step 1: Access Power Query Editor:
- Launch Power BI Desktop and load the dataset using the Get data option in the Home tab
- Once the data is loaded to Power BI, navigate to the Fields pane, right-click on the table, and choose Edit Query to open Power Query Editor.
You can alternatively navigate to Power Query Editor by clicking on the Transformation Data button while loading the dataset.
Power Query Editor opens in a new window with table details.
Step 2: Data Transformation:
- In Power Query Editor, you can perform various data transformations, including filtering rows, renaming columns, changing data types, adding custom columns, merging tables, and more.
- With Power Query Editor, you can additionally do the following: Add Column, Remove columns, Merge Queries, Append Queries, and replace blank values.
- When you perform Data Transformation, Power BI generates Power BI Query Language, which can be viewed in the formula bar. This language allows you to perform various operations on your data, such as filtering, merging, shaping, and creating custom calculations.
You may face Power BI Query Errors while applying data transformation, resulting from incorrect logic, data transformation errors, or data issues. You can handle them with proper error-handling mechanisms and data profile tooling.
Step 3:
- Power Query supports many data sources, including databases (SQL Server, Oracle, MySQL), Excel files, CSV files, JSON, SharePoint, web services, and more.
- You can connect to multiple data sources and combine them in Power Query. You can also define the data source settings for your dataset.
- Power BI Query Parameters can be used to create dynamic and reusable data source connections by defining parameters.
Step 4: Query Steps:
- Power Query keeps track of the steps you perform on your data in a series of query steps in the APPLIED STEPS section.
- You can view and modify these steps to refine your data transformation process.
- Steps are executed in order, and you can add or remove steps as needed.
Step 5:
- After transforming the data in Power Query Editor, click Close & Apply to load the data into the Power BI model.
- You can load data to the data model or create a connection without importing.
- Power BI Query Caching can be an effective tool to improve data loading and query performance.
Step 6:
- Power BI Query Editor enables you to set up data refresh schedules to keep your data up to date.
In the next section, we will see how to use the Editor through different examples.
Examples
This section will demonstrate how to use Power BI Query Editor in a step-by-step process with examples.
Example #1
In this example, we will use Power Query Editor to perform data transformation changes using the Top_1000_Companies_Dataset data. This dataset contains the details of the Top 1000 companies worldwide, such as their employees, revenues, social profiles, and other details.
To use the Power BI Query Editor, follow the steps highlighted below:
Step 1: Open the Power BI Desktop and select the Get data option under the Home tab. Choose a data source option from the list to import data to Power BI Desktop.
Step 2: Click the Load button. It is to load the dataset to Power BI.
Step 3: Once the data is loaded, navigate to the Fields pane to view the dataset.
Step 4:
- Select the table in the Fields pane.
- Right-click on the table.
- Choose the Edit Query option.
It will open Power BI Query Editor with the table details.
Step 5: Choose a column from the table and click on Replace Values. In this case, we have chosen a state column to replace all the blank values with ALL using Replace Values.
Step 6: A pop-up window will appear Once you click the Replace Values button. Provide the value to be replaced with and click on OK.
Now, you will notice all the blank values are replaced with ALL. Also, the Power Query language shows the replaced value ALL in the formula bar.
Step 7: Select a column and choose Data Type as Decimal Number to apply data type changes. Here, we are changing the data type from Text to Decimal Number.
You will see an Error message while changing the data type.
It is the Power BI query Error resulting from incorrect data transformation logic, as shown below.
Step 8: Select a column from the table and click on Rename. We have renamed the column Founded to Foundation Year using the Rename option.
Once the column is renamed, you will see the renamed column in the table. Also, every time you apply any transformation, it gets recorded in APPLIED STEPS.
Once all the changes are applied, choose the Close & Apply option under the Home tab. It will save all the changes you have made to the Power BI Query Editor dataset and navigate you to the Power BI Desktop.
Step 9: Navigate to the Fields pane in Power BI Desktop to view the changes in the dataset.
Example #2
In this example, we will use Power Query Editor to perform data transformation changes using the AdidasSalesdata data. The dataset contains the sales details of Adidas across all the stores in the United States for various brands.
To use the Power BI Query Editor, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the AdidasSalesdata dataset using the Get Data option, and click the Load button.
It will load the data to Power BI Desktop.
Once the data is loaded, you can view it by navigating to the Fields pane.
Step 2:
- Select the table in the Fields pane.
- Right-click on the table.
- Choose the Edit Query option.
It will open Power BI Query Editor with the table details.
You can see the table columns along with the dataset in Power BI Query Editor.
Step 3: Select a column and choose the appropriate Data Type. In this case, we want to change the data type of the Operating Profit column from a Decimal Number to a Fixed decimal number.
A pop-up screen is displayed to confirm the Change Column Type. Choose the Replace current option.
Step 4: Repeat the step 3 to apply data transformation. In this case, we want to change the data type of the Operating Margin column from Decimal Number to Percentage.
A pop-up screen is displayed to confirm the Change Column Type. Choose the Replace current option.
Step 5: Navigate to Add Column tab and choose Custom Column to create a new column in the dataset.
Provide the name of the column and enter the formula for the new column. Here we have named the column as Country with static value as United States. Click on the OK button to save changes.
Now you can view the newly created column with values as per our specification i.e. United States.
Step 6: Navigate to the APPLIED STEPS to view all the data transformations you have applied to the dataset.
Note: Power Query records all the individual steps you perform in the form of Power Query language and the steps.
Step 7: Once all the changes are applied, choose the Close & Apply option under the Home tab. This will save all the changes you have made to the dataset in the Power BI Query Editor and navigate you to the Power BI Desktop.
Step 8: Navigate to the Fields pane in Power BI Desktop to view the changes in the dataset.
Important Things to Note
- Power Query is a powerful tool for data preparation, allowing you to clean and shape your data before analysis. Proper data preparation is crucial for accurate and meaningful insights.
- Power Query optimizes data retrieval by pushing as much of the data transformation back to the source (query folding). It can improve performance, especially for large datasets.
- Power Query supports various data sources, making it versatile for connecting to various data platforms.
- Learning the M language can help you perform advanced data transformations and create custom expressions.
- Understanding and managing query steps is important for maintaining data transformation workflows and troubleshooting issues.
Frequently Asked Questions (FAQs)
Query folding in Power BI refers to the process where Power BI tries to push as much of the data transformation and filtering operations back to the data source (e.g., a database or a web service) rather than performing those operations within Power BI itself. This optimization can lead to more efficient and faster data retrieval and processing. When query folding occurs, Power BI generates SQL queries or queries in the source system’s native language that include filtering and transformation operations. It is a critical optimization technique to minimize data transfer between Power BI and the data source.
An append query in Power BI is a data transformation operation performed in the Power Query Editor. It allows you to combine or concatenate multiple tables or data sources vertically. It is useful when you have multiple data sources or tables with the same structure and want to stack them on top of each other to create a single unified table.
Merge query in Power BI is a feature in Power Query Editor that allows you to combine or merge two or more tables based on common columns. You can perform inner joins, outer joins, and other types of merges to create a single table that combines data from multiple sources or tables It helps create comprehensive datasets for analysis.
To remove duplicates in Power BI Query Editor, follow these steps:
• In the Power BI Desktop, navigate to the Fields pane. Select the table, right-click it, and choose the Edit Query option from the menu bar.
Once the Power BI Query Editor opens up, select the column(s) you want to remove duplicates from.
Click on the Remove Rows dropdown and select the Remove Duplicates option.
• Power Query Editor will remove the duplicate rows based on your selection. You can view the same in your dataset and the APPLIED STEPS.
• Click on Close & Apply to apply your Power BI model changes.
• It will remove duplicate rows from your data in the Power BI model.
Recommended Articles
This has been a guide to Power BI Query. We discuss how to use power query editor to perform data transformation, with examples. You can learn more from the following articles –
Leave a Reply