Power Query in Power BI: Ultimate Guide - AnalyticsLearn (2024)

In this guide, we will be looking at the detailed guide on Power Query in Power BI with implementing steps and examples of it

Power Query in Power BI is a powerful ETL (Extract, Transform, Load) tool that allows users to connect, clean, and transform data before loading it into Power BI for analysis and visualization.

This guide provides a detailed overview of Power Query’s features, step-by-step instructions on using it, and examples to illustrate its capabilities.

Related Article: What is KPI in Power BI? – Comprehensive Guide

What is Power Query?

Source: Microsoft Power BI

Power Query is an intuitive data transformation and data preparation tool integrated into Power BI. It enables users to:

• Connect to a wide range of data sources.
• Clean and transform data using a user-friendly interface.
• Load transformed data into Power BI for further analysis.

Key Features of Power Query

1. Data Connectivity: Connect to multiple data sources including databases, web services, and various file formats.
2. Data Transformation: Apply various transformations such as filtering, sorting, merging, and pivoting data.
3. User Interface: An easy-to-use graphical interface that allows users to perform transformations without writing code.
4. M Language: For advanced users, Power Query offers the M language for complex transformations.

Steps for Power Query in Power BI

Step 1: Accessing Power Query

1. Open Power BI Desktop: Launch Power BI Desktop to start working with Power Query.
2. Get Data: Click on the “Home” tab and select “Get Data”. This opens a window where you can choose your data source.

Step 2: Connecting to a Data Source

1. Select a Data Source: Choose a data source such as Excel, SQL Server, or a web page. For example, select “Excel” to connect to an Excel file.
2. Load Data: Navigate to the file location and select the file. Click “Load” to import the data into Power Query Editor.

Step 3: Using Power Query Editor

Once the data is loaded, Power Query Editor opens. Here, you can perform various data transformations.

1. Preview Pane: View a sample of the data to understand its structure.
2. Query Settings: The right pane shows the applied steps and properties of the query.
3. Ribbon: The top menu offers various transformation options.

Step 4: Performing Data Transformations

1. Removing Columns

1. Select Columns: Click on the columns you want to remove.
2. Remove Columns: Go to the “Home” tab and click “Remove Columns”.

2.Filtering Rows

1. Select Column: Click on the column header you want to filter.
2. Apply Filter: Click the dropdown arrow in the column header, choose the filter criteria (e.g., “Greater than 1000”), and apply.

3. Splitting Columns

1. Select Column: Click on the column you want to split.
2. Split Column: Go to the “Transform” tab, select “Split Column”, and choose the delimiter or number of characters.

4. Merging Queries

1. Merge Queries: Go to the “Home” tab, click “Merge Queries”, and select the queries you want to merge.
2. Join Type: Choose the type of join (e.g., Inner Join, Left Outer Join).

5. Grouping Data

1. Group By: Go to the “Transform” tab, click “Group By”, and select the column(s) to group by.
2. Aggregation: Choose the aggregation method (e.g., Sum, Average).

Step 5: Advanced Transformations Using M Language

For complex transformations, you can use the M language.

Example: Adding a Custom Column

1. Custom Column: Go to the “Add Column” tab and click “Custom Column”.
2. Write M Code: In the formula box, write your M code. For example, if [Sales] > 1000 then “High” else “Low”.

Step 6: Loading Data into Power BI

1. Close & Apply: Once you’ve finished transforming the data, click “Close & Apply” in the Power Query Editor.
2. Data Load: The transformed data is loaded into Power BI for further analysis and visualization.

Practical Examples of Power Query

Example 1: Combining Data from Multiple Excel Sheets

1. Connect to Excel: Import data from multiple sheets within an Excel file.
2. Append Queries: Use “Append Queries” to combine the data from different sheets.
3. Clean Data: Apply necessary transformations like removing unnecessary columns and filtering rows.
4. Load Data: Load the combined and cleaned data into Power BI.

Example 2: Extracting Data from a Web Page

1. Connect to Web Data: Select “Web” from the “Get Data” options and enter the URL of the webpage.
2. Select Tables: Choose the table containing the data you need.
3. Transform Data: Apply transformations such as renaming columns, changing data types, and filtering rows.
4. Load Data: Load the transformed data into Power BI.

Example 3: Data Integration from Multiple Sources

Objective: Combine data from a SQL Server database and an Excel file.

1. Connect to SQL Server: Select “SQL Server” from “Get Data” and provide the server and database name. Import the required table.
2. Connect to Excel: Select “Excel” from “Get Data” and import the necessary sheet.
3. Merge Queries: In the Power Query Editor, use “Merge Queries” to combine the SQL data with the Excel data. Choose the appropriate join type and matching columns.
4. Transform Data: Apply transformations such as renaming columns, changing data types, and filtering rows.
5. Load Data: Load the combined and transformed data into Power BI.

Example 4: Extracting and Transforming JSON Data

Objective: Extract data from a JSON API and transform it for analysis.

1. Connect to Web: Select “Web” from “Get Data” and enter the API URL.
2. Parse JSON: Once the data is loaded, click on the “Record” or “List” to navigate through the JSON hierarchy.
3. Expand Data: Use the “Expand” option to flatten the JSON structure into a tabular format.
4. Transform Data: Apply necessary transformations like renaming columns, filtering rows, and changing data types.
5. Load Data: Load the transformed data into Power BI for further analysis.

Best Practices in Power Query

1. Plan Transformations: Outline the steps required before starting the transformation process.
2. Descriptive Naming: Use meaningful names for queries and steps for better readability.
3. Minimize Steps: Combine multiple transformation steps to improve performance.
4. Regular Updates: Regularly update queries to accommodate changes in the data source.

Conclusion

Power Query in Power BI is an essential tool for efficient data preparation and transformation.

Its user-friendly interface, combined with powerful capabilities, makes it accessible to both novice and advanced users.

By following this guide and leveraging Power Query’s features, you can streamline your data workflows and enhance your data analysis capabilities in Power BI.