Power BI Data Transformation Basics for Insights

Introduction

Data is the lifeblood of business intelligence, and Microsoft's Power BI is a powerful tool that helps businesses turn raw data into valuable insights. One of the key components of Power BI is Power Query, a data transformation and preparation tool. In this article, we will explore the basics of Power Query with examples to illustrate its capabilities and how it can help you clean and shape your data for analysis.

What is Power Query?

Power Query is a data connectivity and transformation tool that is used to connect, clean, and shape data from various sources before loading it into Power BI for analysis and visualization. It's part of the Power BI suite and is also available in other Microsoft products like Excel.

Getting Started with Power Query

Before we dive into examples, here are the basic steps to get started with Power Query in Power BI.

• Connect to Data Source:
• Open Power BI Desktop.
• Go to the "Home" tab and click on "Get Data."
• Choose a data source, such as a file (Excel, CSV), database, web service, or online source.

Query Editor

After connecting to a data source, you'll enter the Query Editor. This is where you perform data transformations.

Common Power Query Transformations with Examples

1. Filtering Rows. To filter rows based on a condition.

Table.SelectRows(Source, each [ColumnName] = Value)

Example

Table.SelectRows(Sales, each [ProductCategory] = "Electronics")

2. Removing Columns: To remove unnecessary columns.

Table.RemoveColumns(Source, {"ColumnName1", "ColumnName2"})

Example

Table.RemoveColumns(Orders, {"OrderID", "CustomerID"})

3. Renaming Columns: To rename columns.

Table.RenameColumns(Source, {{"OldName1", "NewName1"}, {"OldName2", "NewName2"}})

Example

Table.RenameColumns(Products, {{"ProductID", "ID"}, {"ProductName", "Name"}})

4. Sorting Rows: To sort rows based on a column.

Table.Sort(Source,{{"ColumnName", Order.Ascending}})

Example

Table.Sort(Employees, {{"LastName", Order.Ascending}})

5. Grouping and Aggregating Data: To group and aggregate data by a column.

Table.Group(Source, {"GroupColumn"}, {{"AggregateColumn", each List.Sum([ColumnName]), "Sum"}})

Example

Table.Group(Sales, {"ProductID"}, {{"TotalSales", each List.Sum([SalesAmount]), "Sum"}})

6. Pivoting and Unpivoting Columns:

1. To pivot columns

   Table.Pivot(Source, [PivotColumn], [ColumnToAggregate], [AggregateFunction])

   Example

   Table.Pivot(Products, "Category", "Price", List.Sum)

2. To unpivot columns

   Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")

   Example

   Table.UnpivotOtherColumns(Sales, {"OrderID"}, "Product", "Quantity")

7. Combining Queries: To combine multiple queries.

Table.Combine({Query1, Query2})

Example

Table.Combine({Sales2019, Sales2020})

8. Adding Custom Columns: To add a custom column using a formula.

Table.AddColumn(Source, "NewColumnName", each [Column1] + [Column2])

Example

Table.AddColumn(Orders, "TotalPrice", each [Quantity] * [UnitPrice])

9. Data Types Conversion: To change data types.

Table.TransformColumnTypes(Source, {{"ColumnName", type}})

Example

Table.TransformColumnTypes(Products, {{"Price", type number}})

These are some of the fundamental data transformations you can perform using Power Query in Power BI. Power Query's user-friendly interface and flexibility make it an invaluable tool for data cleaning and transformation, setting the stage for meaningful data analysis and visualization in Power BI.