Power BI Power Query Examples: Mastering Data Transformation

Disclaimer: This content is provided for informational purposes only and does not intend to substitute financial, educational, health, nutritional, medical, legal, etc advice provided by a professional.

Power BI Power Query Examples: Mastering Data Transformation

Welcome to our comprehensive guide on Power Query examples in Power BI! In this article, we will explore the power and versatility of Power Query, a powerful data transformation tool that can simplify and automate various data activities. Whether you are a beginner or an advanced user, this guide will provide you with practical examples and tips to unlock the full potential of Power Query in Power BI.

Connect to data

One of the first steps in data analysis is to connect to your data source. Power Query makes it easy to connect to various data sources, such as Excel spreadsheets, CSV files, databases, and web sources. Let's look at an example of how to connect to an Excel spreadsheet:

let
    
    Source = Excel.Workbook(File.Contents("C:\\Path\\To\\Your\\File.xlsx"), null, true),
    
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    
    ...

In this example, we use the 'Excel.Workbook' function to connect to the Excel file located at 'C:\Path\To\Your\File.xlsx'. You can replace this file path with your own file path to connect to your Excel file. Once connected, you can select the specific sheet or table from the Excel file to load into Power BI.

Shape and combine data

Power Query provides powerful tools to shape and combine your data to meet your analysis needs. Let's take a look at an example of how to shape and combine data:

let
    
    Source = ...
    
    
    TransformedData = Table.TransformColumns(Source, {"Column1", each Text.ToUpper(_), type text})
    
    
    ...

In this example, we use the 'Table.TransformColumns' function to transform the 'Column1' in our data source to uppercase text. You can apply various transformations to your data, such as removing columns, renaming columns, changing data types, and applying formulas.

Group rows

Grouping rows is a common task in data analysis, and Power Query makes it easy to group rows based on specific criteria. Here's an example of how to group rows:

let
    
    Source = ...
    
    
    GroupedData = Table.Group(Source, {"Column1"}, {"SumColumn", each List.Sum([Column2]), type number})
    
    
    ...

In this example, we use the 'Table.Group' function to group rows in our data source based on the values in 'Column1'. We also calculate the sum of 'Column2' for each group. You can customize the grouping criteria and the aggregations to meet your specific analysis requirements.

Pivot columns

Pivoting columns is another powerful feature in Power Query that allows you to transform your data from a row-based structure to a column-based structure. Here's an example of how to pivot columns:

let
    
    Source = ...
    
    
    PivotedData = Table.Pivot(Source, {"Column1"}, "Column2", "ValueColumn")
    
    
    ...

In this example, we use the 'Table.Pivot' function to pivot our data source based on the values in 'Column1'. We select 'Column2' as the column to pivot and 'ValueColumn' as the column that contains the values. This allows us to transform our data into a more compact and meaningful format for analysis.

Create custom columns

Creating custom columns is a powerful feature in Power Query that allows you to add calculated columns to your data. Here's an example of how to create custom columns:

let
    
    Source = ...
    
    
    CustomColumns = Table.AddColumn(Source, "CustomColumn", each [Column1] + [Column2])
    
    
    ...

In this example, we use the 'Table.AddColumn' function to add a custom column called 'CustomColumn' to our data source. We define the calculation for the custom column using the values from 'Column1' and 'Column2'. You can create custom columns based on simple arithmetic calculations, conditional statements, or complex formulas.

Query formulas

Query formulas are an advanced feature in Power Query that allows you to create complex data transformation logic using the M formula language. Let's take a look at an example of how to use query formulas:

let
    
    Source = ...
    
    
    CustomFunction = (x) => x * 2,
    
    
    AppliedFunction = Table.TransformColumns(Source, {"Column1", each CustomFunction(_), type number})
    
    
    ...

In this example, we define a custom function called 'CustomFunction' that multiplies a value by 2. We then use the 'Table.TransformColumns' function to apply the custom function to 'Column1' in our data source. You can create your own custom functions and use them in your query formulas to perform complex data transformations.

Conclusion

Power Query is a powerful tool that can simplify and automate various data activities in Power BI. In this article, we explored several examples of how to perform common query tasks using Power Query in Power BI. We covered connecting to data sources, shaping and combining data, grouping rows, pivoting columns, creating custom columns, and using query formulas. By mastering these examples, you will be able to unlock the full potential of Power Query and enhance your data transformation skills. Start exploring the possibilities of Power Query in Power BI today and take your data analysis to the next level!