Data Transformation in Power BI

1. Introduction to Power Query

In Microsoft Power BI, Power Query is a data connection technology that allows users to extract, transform, and load (ETL) data from various sources efficiently. It also helps in preparing data for analysis with minimal coding.

Key Features of Power Query:

Data Connectivity: Connects to multiple data sources such as databases, web services, Excel files, and APIs.
Data Transformation: Provides a user-friendly interface for cleaning, merging, filtering, and reshaping data.
Automation: Automates repetitive data preparation tasks with queries that can be refreshed easily.
M Language: A functional language that allows for advanced scripting and customization.

2. Data Transformation in Power Query Editor

The Power Query Editor provides a graphical interface for transforming and reshaping data without needing to write code manually.

Key Sections of the Power Query Editor:

Query Pane: Displays a list of queries in the workbook.
Data Preview Pane: Shows a preview of the selected query’s data.
Applied Steps Pane: Tracks all transformation steps applied to the data.
Formula Bar: Displays the M code corresponding to each transformation.

Common Transformations in Power Query Editor:

Sorting and Filtering: Organizing and refining data by conditions.
Removing Columns: Eliminating unnecessary columns to clean up datasets.
Merging Queries: Combining data from different tables using inner, outer, or left joins.
Adding Custom Columns: Creating new columns using formulas or expressions.
Using Parameters: Dynamically modifying queries based on user input.

3. Advanced Query Editor Features

Power Query provides the Advanced Editor for more complex data transformations where users can write and modify M code.

Key Features of the Advanced Editor:

Direct M Code Editing: Allows users to customize transformations beyond the graphical UI.
Reusable Functions: Enables creating custom functions for repetitive tasks.
Conditional Logic: Implements if-else statements for advanced data processing.
Performance Optimization: Optimizes queries by reducing unnecessary transformations and improving refresh speed.

Example of M Code in Advanced Editor:

let 
    Source = Excel.Workbook(File.Contents("C:\\Data.xlsx")), 
    Table = Source{[Name="Sheet1"]}[Content],   
    FilteredRows = Table.SelectRows(Table, each [Sales] > 1000), 
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"OldName", "NewName"}}) 

in 
    RenamedColumns

This script explains:

Loads data from an Excel file.
Filters rows where “Sales” is greater than 1000.
Renames a column.

Request more information

Data Transformation in Power BI