Lesson 1: Getting Started with M Code in Power BI

Welcome to the first lesson of our course, "Gain a Deep Understanding of M Code in Power BI." In this lesson, we will introduce you to M code, an essential component in Power BI that enhances data transformations and analysis.

Overview of M Code

M Code is the language used by Power Query in Power BI for data manipulation and transformation. It is a functional, case-sensitive language designed to handle complex data shaping tasks efficiently. Understanding M Code allows you to perform intricate data transformations that may not be straightforward with the Power Query UI alone.

Why Learn M Code?

• Advanced Data Transformation: Perform complex data shaping that isn't possible through the Power Query UI.
• Automation: Automate repetitive tasks with custom scripts.
• Customization: Tailor data transformations exactly to your requirements.
• Efficiency: Improve performance of data loading and processing.

Setting Up

Accessing the Power Query Editor

1. Open Power BI Desktop.
2. Load a dataset by either uploading a file (e.g., CSV, Excel) or connecting to a data source (e.g., database).
3. In the ‘Home’ tab, click on Transform Data to open the Power Query Editor.

Power Query Editor Interface Explained

• Ribbon: Contains various tabs with commands grouped by functionality.
• Queries Pane: Lists all the queries in your report.
• Data View: Shows a preview of the data for the selected query.
• Formula Bar: Displays the M code for the selected transformation step.
• Applied Steps: Lists all the transformations applied to the query.

Basic Syntax and Structure of M Code

Variables and Data Types

M Code allows multiple data types, such as text, number, date/time, and more. Variables are created using the let and in keywords.

let
    Source = "Hello, Power BI!",
    Length = Text.Length(Source)
in
    Length

Common Functions

Text Functions

• Text.Combine: Combines a list of text values.
• Text.Lower: Converts text to lower case.

let
    TextList = {"Hello", "World"},
    CombinedText = Text.Combine(TextList, " ")
in
    Text.Lower(CombinedText)

Number Functions

• Number.Round: Rounds a number to a specified number of decimal places.
• Number.Abs: Returns the absolute value of a number.

let
    NegativeNumber = -5.678,
    PositiveNumber = Number.Abs(NegativeNumber)
in
    Number.Round(PositiveNumber, 2)

Date Functions

• DateTime.Date: Extracts the date part from a DateTime value.
• Date.AddDays: Adds a specified number of days to a date.

let
    CurrentDate = DateTime.LocalNow(),
    DateOnly = DateTime.Date(CurrentDate),
    FutureDate = Date.AddDays(DateOnly, 10)
in
    FutureDate

Transforming Data: A Real-Life Example

Let's go through a practical example where we import a dataset and apply transformations using M code.

Step-by-Step Example

1. Load the Dataset

   Load a sample sales dataset which includes columns like Date, Product, Sales.

2. Open Power Query Editor

   Navigate to the Power Query Editor as described earlier.

3. Basic Transformation

   • Renaming Columns:

     let
         Source = Excel.Workbook(File.Contents("C:\SalesData.xlsx"), null, true),
         Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
         RenamedColumns = Table.RenameColumns(Sheet, {{"Column1", "Date"}, {"Column2", "Product"}, {"Column3", "Sales"}})
     in
         RenamedColumns

   • Filtering Rows:

     let
         Source = ...,
         RenamedColumns = ...,
         FilteredRows = Table.SelectRows(RenamedColumns, each [Sales] > 100)
     in
         FilteredRows

   • Creating a New Column:

     let
         Source = ...,
         RenamedColumns = ...,
         FilteredRows = ...,
         AddedColumn = Table.AddColumn(FilteredRows, "SalesWithTax", each [Sales] * 1.2)
     in
         AddedColumn

Applying custom M code in Power Query Editor

1. Navigating to Advanced Editor

   • In the Power Query Editor, go to the View tab and click on Advanced Editor.

2. Editing the M Code

   • You can directly type or paste the M Code into the Advanced Editor. Be sure to update the steps accordingly.

Summary

In this lesson, we have covered the basics of M Code in Power BI, including its syntax, key functions, and how to perform simple data transformations. Understanding the fundamentals of M Code will set a strong foundation for more advanced data shaping tasks in Power BI.

Your homework is to practice loading a dataset and applying basic transformations using the M code snippets provided in this lesson. Happy coding!

---

Ready to move forward? In the next lesson, we will dive deeper into advanced data transformations and optimization techniques using M Code. Stay tuned!

Lesson #2: Understanding Data Types and Structures

In this lesson, we will explore the various data types and structures within M code in Power BI. Understanding these concepts is crucial for successful data manipulation and transformation.

Table of Contents

1. Introduction to Data Types
2. Primary Data Types in M
3. Complex Data Types in M
4. Introduction to Data Structures
5. Using Data Types and Structures in M
6. Real-Life Examples

1. Introduction to Data Types

Data types define the kind of value a variable can hold. In M, the data types are similar to those in other programming languages but are tailored to data transformation needs within Power BI.

2. Primary Data Types in M

M code supports several primary data types:

• Number: Represents both integers and floating-point numbers.
• Text: Represents string values.
• Logical: Boolean values, true or false.
• Null: Represents an absence of a value.
• DateTime: Represents dates and times.

Number

let
    NumberExample = 42,
    FloatExample = 42.5
in
    FloatExample

Text

let
    TextExample = "Hello, Power BI!"
in
    TextExample

Logical

let
    LogicalExample = true
in
    LogicalExample

Null

let
    NullExample = null
in
    NullExample

DateTime

let
    DateExample = #date(2023, 10, 5),
    TimeExample = #datetime(2023, 10, 5, 15, 30, 0)
in
    TimeExample

3. Complex Data Types in M

Beyond primary data types, M supports more complex structures:

• Record: A collection of named fields.
• List: A collection of values.
• Table: A collection of rows and columns.

Record

let
    RecordExample = [ Name = "John Doe", Age = 30 ]
in
    RecordExample

List

let
    ListExample = {1, 2, 3, "A", "B", "C"}
in
    ListExample

Table

let
    TableExample = 
    #table(
        { "ID", "Name" }, 
        { {1, "Alice"}, {2, "Bob"} }
    )
in
    TableExample

4. Introduction to Data Structures

Data structures organize data in a way that enables efficient access and modification. In M, these include lists, records, and tables.

• Lists: Ordered collections that can contain items of different types.
• Records: Unordered collections with named fields, similar to structs in other languages.
• Tables: Collections of rows and columns, similar to data frames or spreadsheets.

Combining Data Structures

It's common to combine data structures to model complex data:

let
    EmployeeRecord = [
        Name = "Jane Smith",
        Age = 29,
        Skills = {"C#", "M", "SQL"}
    ],
    DepartmentTable = #table(
        {"DepartmentID", "DepartmentName"},
        { {1, "HR"}, {2, "Finance"} }
    )
in
    EmployeeRecord

5. Using Data Types and Structures in M

Understanding how to leverage data types and structures allows for effective data transformation in M.

Example: Filtering a Table by a Condition

let
    Source = Table.FromRecords({
        [ID = 1, Name = "Alice", Age = 28],
        [ID = 2, Name = "Bob", Age = 34],
        [ID = 3, Name = "Charlie", Age = 22]
    }),
    Filtered = Table.SelectRows(Source, each [Age] > 25)
in
    Filtered

Example: Transforming Data within a List

let
    Numbers = {1, 2, 3, 4, 5},
    SquaredNumbers = List.Transform(Numbers, each _ * _)
in
    SquaredNumbers

6. Real-Life Examples

Example 1: Sales Data Transformation

Let's assume you have a sales table, and you need to calculate the total sales amount:

let
    SalesTable = Table.FromRecords({
        [Product = "A", Quantity = 10, UnitPrice = 20],
        [Product = "B", Quantity = 5, UnitPrice = 15]
    }),
    AddTotalSales = Table.AddColumn(SalesTable, "TotalSales", each [Quantity] * [UnitPrice])
in
    AddTotalSales

Example 2: Merging Customer and Orders Tables

let
    Customers = Table.FromRecords({
        [CustomerID = 1, CustomerName = "Alice"],
        [CustomerID = 2, CustomerName = "Bob"]
    }),
    Orders = Table.FromRecords({
        [OrderID = 101, CustomerID = 1, Amount = 250],
        [OrderID = 102, CustomerID = 2, Amount = 180]
    }),
    MergedTables = Table.Join(Customers, "CustomerID", Orders, "CustomerID")
in
    MergedTables

Conclusion

Understanding data types and structures is foundational for mastering M code in Power BI. By grasping these concepts, you can effectively manipulate and transform your data, leading to more insightful analyses.

Lesson #3: Advanced Data Transformation Techniques

Overview

Welcome to Lesson #3 of your course on M Code in Power BI. In this lesson, we will explore advanced data transformation techniques in Power BI using M Code. We aim to equip you with the skills to perform complex data manipulations, making your data more meaningful and analyzable.

Key Concepts

1. Understanding M Functions
2. Advanced Query Operations
3. Conditional and Custom Columns
4. Using Parameters
5. Optimizing Performance

---

1. Understanding M Functions

M functions are powerful tools in Power BI for transforming data. They provide a versatile way to manipulate, shape, and analyze data sets.

Commonly Used Functions

• Text Functions: E.g. Text.Upper, Text.Proper, Text.Contains
• Date & Time Functions: E.g. DateTime.LocalNow, Date.AddDays
• List Functions: E.g. List.Sum, List.Max
• Table Functions: E.g. Table.SelectRows, Table.AddColumn

Example: To extract the year from a date:

let
    Source = Table.FromRecords({[Date = #date(2023, 1, 1)]}),
    YearColumn = Table.AddColumn(Source, "Year", each Date.Year([Date]))
in
    YearColumn

2. Advanced Query Operations

Gain control of your transformations through advanced query operations in M code.

Grouping Data

Grouping data helps condense large datasets into summaries.

Example: Group sales data by product category to calculate total sales per category.

let
    SalesData = Table.FromRecords({
        [Product="A", Sales=100],
        [Product="B", Sales=200],
        [Product="A", Sales=150]
    }),
    GroupedData = Table.Group(SalesData, {"Product"}, {{"TotalSales", each List.Sum([Sales]), type number}})
in
    GroupedData

Merging Queries

Merge multiple tables to create enriched datasets.

Example: Combine customer details with sales data.

let
    Customers = Table.FromRecords({[CustomerID=1, Name="John Doe"], [CustomerID=2, Name="Jane Smith"]}),
    Sales = Table.FromRecords({[CustomerID=1, Sales=300], [CustomerID=2, Sales=500]}),
    MergedData = Table.NestedJoin(Customers, "CustomerID", Sales, "CustomerID", "SalesDetails", JoinKind.Inner),
    ExpandedData = Table.ExpandTableColumn(MergedData, "SalesDetails", {"Sales"}, {"TotalSales"})
in
    ExpandedData

3. Conditional and Custom Columns

Enhance your data model by adding conditional and custom columns using M code.

Conditional Columns

Use conditional logic to create new columns.

Example: Categorize sales as "High" or "Low" based on a threshold.

let
    SalesData = Table.FromRecords({[Product="A", Sales=100], [Product="B", Sales=500]}),
    AddedConditionalColumn = Table.AddColumn(SalesData, "SalesCategory", each if [Sales] > 300 then "High" else "Low")
in
    AddedConditionalColumn

Custom Columns

Create custom columns for more specific needs.

Example: Add a column showing the length of a product name.

let
    ProductData = Table.FromRecords({[Product="Product A"], [Product="Product B"]}),
    CustomColumn = Table.AddColumn(ProductData, "NameLength", each Text.Length([Product]))
in
    CustomColumn

4. Using Parameters

Parameters allow for dynamic and reusable queries.

Defining Parameters

Define parameters to make your queries flexible.

Example: Create a parameter for filtering data by a specific year.

let
    YearParameter = 2022,
    SalesData = Table.FromRecords({
        [Year=2021, Sales=100],
        [Year=2022, Sales=200]
    }),
    FilteredData = Table.SelectRows(SalesData, each [Year] = YearParameter)
in
    FilteredData

5. Optimizing Performance

Optimizing M code is crucial for handling large datasets efficiently.

Best Practices

• Reduce Step Complexity: Simplify transformation steps where possible.
• Avoid Multiple Evaluations: Ensure source data tables are referenced just once.
• Minimize Calculated Columns: Limit the use of calculated columns in large tables.

Example: Avoid unnecessary steps by combining transformations.

let
    Data = Table.FromRecords({
        [Category="A", Sales=100],
        [Category="B", Sales=300]
    }),
    OptimizedQuery = Table.AddColumn(Data, "SalesCategory", each if [Sales] > 150 then "High" else "Low")
in
    OptimizedQuery

---

Conclusion

This lesson covered advanced data transformation techniques using M code in Power BI. Understanding and applying these techniques will enable you to derive more meaningful insights from your data. Practice these methods to become proficient in handling various data transformation scenarios.

Happy transforming!

Lesson 4: Writing Custom Functions in M Code

Introduction

In this lesson, we will dive into how to create custom functions using M code in Power BI. Custom functions can streamline your data transformations by making your code reusable, modular, and easier to maintain. This lesson builds on the concepts of data types, structures, and advanced data transformation techniques discussed in the previous lessons.

What is a Custom Function?

A custom function is a reusable block of M code that takes one or more inputs (parameters), performs some operations, and returns an output. These functions can be called multiple times within your data transformation scripts, reducing redundancy and improving code clarity.

Syntax of a Custom Function

The syntax for creating a custom function in M code is as follows:

function_name = (parameter1 as datatype, parameter2 as datatype, ...) as returntype => 
let
    // Your transformation logic here
in
    // Final computed value

Key Elements

• function_name: Name of the function, which can be used to call it.
• parameter1, parameter2, ...: Inputs required by the function.
• datatype: Data type of each parameter (e.g., number, text, list).
• returntype: Data type of the value returned by the function.
• let: Section where intermediate calculations are performed.
• in: Section that defines the final output of the function.

Example: Calculating Sales Tax

Let's create a simple custom function to calculate the sales tax for a given amount.

Function Definition

CalculateSalesTax = (amount as number, taxRate as number) as number =>
let
    taxAmount = amount * taxRate,
    totalAmount = amount + taxAmount
in
    totalAmount

Using the Function

To use this function within your transformation script, call it with the required parameters:

let
    SalesAmounts = {100, 200, 300},
    TaxRate = 0.05,
    SalesWithTax = List.Transform(SalesAmounts, each CalculateSalesTax(_, TaxRate))
in
    SalesWithTax

In this example, the CalculateSalesTax function is applied to each element in the SalesAmounts list with a TaxRate of 5%.

Real-Life Example: Data Cleansing

Consider a scenario where you need to clean a dataset by trimming whitespace and converting text to proper case. You can create a custom function to handle this transformation.

Function Definition

CleanText = (inputText as text) as text =>
let
    trimmedText = Text.Trim(inputText),
    properCaseText = Text.Proper(trimmedText)
in
    properCaseText

Using the Function

Assume you have a table with a ProductName column that needs cleaning:

let
    // Step to access your data (e.g., from a database)
    Source = YourDataSource,
    CleanedData = Table.TransformColumns(Source, {"ProductName", each CleanText(_), type text})
in
    CleanedData

This transforms the ProductName column by applying the CleanText function to each entry, ensuring that all product names are properly trimmed and capitalized.

Benefits of Custom Functions

Reusability

Custom functions allow you to encapsulate logic that can be reused across multiple queries, reducing redundancy.

Modularity

Breaking down complex transformations into smaller, manageable functions makes your code easier to understand and maintain.

Consistency

Using custom functions ensures consistent application of transformation logic across all instances where it is required.

Conclusion

Creating custom functions in M code enables you to write more efficient, modular, and maintainable scripts for your Power BI data transformations. By encapsulating repetitive logic into functions, you can streamline your workflows and make your data transformations more scalable and error-free.

In the next lesson, we will explore how to debug and optimize your M code to ensure peak performance in your Power BI reports.

Happy coding!

Lesson 5: Optimizing and Debugging M Code

Introduction

In this lesson, you will learn how to optimize and debug M code effectively. Optimizing ensures your data transformations run efficiently, while debugging allows you to identify and fix issues in your code. We'll explore practical techniques and look at real-life examples to illustrate best practices.

Section 1: Importance of Optimization

Performance Considerations

Optimization is crucial for making your queries and transformations run faster and more efficiently. This is especially important when dealing with large datasets where inefficient code can lead to significant slowdowns.

Techniques for Optimization

1. Reduce Row Counts Early: Perform filtering operations early to minimize the number of rows in subsequent operations.

   // Inefficient
   let
       Source = YourDataSource,
       Transformed = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
       Filtered = Table.SelectRows(Transformed, each [Column1] = "SpecificValue")
   in
       Filtered
   
   // Efficient
   let
       Source = YourDataSource,
       Filtered = Table.SelectRows(Source, each [Column1] = "SpecificValue"),
       Transformed = Table.TransformColumnTypes(Filtered, {{"Column1", type text}})
   in
       Transformed

2. Minimize Step Dependencies: Avoid unnecessary steps dependent on one another.

   let
       Source = YourDataSource,
       RemovedColumns = Table.RemoveColumns(Source, {"UnwantedColumn"}),
       Filtered = Table.SelectRows(RemovedColumns, each [Column2] = "Value")
   in
       Filtered
   // Combine into one step if possible
   let
       Source = YourDataSource,
       FilteredRemoved = Table.SelectRows(Source, each [Column2] = "Value" and [UnwantedColumn] != null)
   in
       FilteredRemoved

3. Avoid Recomputing Values: Use intermediate variables to store reusable values.

   // Inefficient
   let
       Source = YourDataSource,
       ComputedColumn1 = Table.AddColumn(Source, "NewColumn", each [Column1] * 2),
       ComputedColumn2 = Table.AddColumn(ComputedColumn1, "AnotherNewColumn", each [Column1] * 2 + 5)
   in
       ComputedColumn2
   
   // Efficient
   let
       Source = YourDataSource,
       ComputedValue = each [Column1] * 2,
       ComputedColumn1 = Table.AddColumn(Source, "NewColumn", ComputedValue),
       ComputedColumn2 = Table.AddColumn(ComputedColumn1, "AnotherNewColumn", each ComputedValue([Column1]) + 5)
   in
       ComputedColumn2

Section 2: Debugging M Code

Identifying Errors

Debugging involves tracing errors and understanding the cause of issues in your code. Errors can arise from syntax issues, incorrect data types, or logical errors.

Common Errors and Fixes

1. Type Mismatch:

   Error: We couldn’t apply the calculated column as the expression.
   Fix: Ensure correct data types are used, e.g., converting text to numbers.

   // Converting text to number
   let
       Source = YourDataSource,
       CorrectedType = Table.TransformColumnTypes(Source, {{"NumberColumn", Int64.Type}})
   in
       CorrectedType

2. Missing or Incorrect Columns:

   Error: The column 'ColumnName' of the table wasn't found.
   Fix: Always check column names and existence before transformations.

   let
       Source = YourDataSource,
       CheckColumns = Table.SelectColumns(Source, {"ExistingColumn", "AnotherColumn"})
   in
       CheckColumns

Debugging Techniques

1. Step-by-Step Execution: Break down your code and validate each step individually.

   let
       Source = YourDataSource,
       Step1 = Table.SelectRows(Source, each [Column1] = "Value"),
       Step2 = Table.AddColumn(Step1, "NewColumn", each [Column2] * 2)
   in
       Step2

   Verify Step1, then proceed to Step2.

2. Use Trace Functions:

   let
       Source = YourDataSource,
       LoggedStep = Table.TransformColumns(Source, {}, ((columnName, number) => 
           if columnName = "Column1" then TraceValue(number, "Column1 Processing") else number))
   in
       LoggedStep

Section 3: Best Practices

Use Descriptive Names

Name your steps and variables descriptively to make your code more readable and maintainable.

let
    Source = YourDataSource,
    FilteredData = Table.SelectRows(Source, each [Status] = "Active"),
    TransformedData = Table.TransformColumnTypes(FilteredData, {{"Amount", Currency.Type}})
in
    TransformedData

Comment Your Code

Include comments to explain complex logic.

let
    // Load data from source
    Source = YourDataSource,
    // Filter active status records
    FilteredData = Table.SelectRows(Source, each [Status] = "Active"),
    // Convert Amount column to currency type
    TransformedData = Table.TransformColumnTypes(FilteredData, {{"Amount", Currency.Type}})
in
    TransformedData

Conclusion

Optimizing and debugging M code are essential skills that can significantly impact the performance and reliability of your data transformations in Power BI. Use the techniques outlined in this lesson to write efficient and error-free M code. Make sure to always review and test each step of your queries to ensure they are performing as expected.