Understanding the Initial Dataset

Introduction

This section will show you how to understand and transform your actual vs. budget data using Power Query M language in Excel. You will learn to import, clean, and transform your dataset, setting the foundation for better data insights and analysis.

Instructions

Step 1: Importing Data

1. Open Excel and navigate to the 'Data' tab.
2. Click on 'Get Data' > 'From File' > 'From Workbook'.
3. Select the Excel file containing your actual vs. budget data.
4. Choose the specific sheet or range containing the data and click 'Load' or 'Transform Data' for more customization.

Step 2: Inspecting the Dataset

Once the data is loaded into Power Query Editor, inspect its structure:

// View the first few rows of the dataset
let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    FirstRows = Table.FirstN(Source, 10)
in
    FirstRows

This script will show you the first 10 rows of your data to get a sense of what it looks like.

Step 3: Data Cleansing

Ensure columns have appropriate data types and handle any missing values:

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    // Convert data types
    ChangedType = Table.TransformColumnTypes(Source, {
        {"Date", type date},
        {"Actual", type number},
        {"Budget", type number},
        {"Category", type text}
    }),
    // Remove rows with missing values
    RemovedRows = Table.RemoveRowsWithErrors(ChangedType)
in
    RemovedRows

Step 4: Adding Calculated Columns

Add a calculated column to see the variance between actual and budget values:

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {
        {"Date", type date},
        {"Actual", type number},
        {"Budget", type number},
        {"Category", type text}
    }),
    RemovedRows = Table.RemoveRowsWithErrors(ChangedType),
    // Add a new column to calculate variance
    AddedCustom = Table.AddColumn(RemovedRows, "Variance", each [Actual] - [Budget], type number)
in
    AddedCustom

Step 5: Grouping Data

Group data by Category to get summarised insights:

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {
        {"Date", type date},
        {"Actual", type number},
        {"Budget", type number},
        {"Category", type text}
    }),
    RemovedRows = Table.RemoveRowsWithErrors(ChangedType),
    AddedCustom = Table.AddColumn(RemovedRows, "Variance", each [Actual] - [Budget], type number),
    // Group by Category
    GroupedData = Table.Group(AddedCustom, {"Category"}, {
        {"Total Actual", each List.Sum([Actual]), type number},
        {"Total Budget", each List.Sum([Budget]), type number},
        {"Total Variance", each List.Sum([Variance]), type number}
    })
in
    GroupedData

Step 6: Loading Transformed Data Back into Excel

Once you've done the necessary transformations, load the data back into Excel:

1. Click on 'Close & Load' in the Power Query Editor.
2. Select 'Close & Load To' to customize how you want the data loaded into Excel (e.g., to a table or pivot table).

Conclusion

You have successfully transformed and analyzed your actual vs. budget data in Excel using Power Query M language. This foundational step sets up a robust structure for further data insights and analysis.

Loading and Shaping Data in Power Query

Loading Data into Power Query

1. Load Actual Data Table

   let
       Source = Excel.Workbook(File.Contents("C:\Path\To\ActualData.xlsx"), null, true),
       Actuals_Sheet = Source{[Name="Actuals"]}[Content],
       ChangedType = Table.TransformColumnTypes(Actuals_Sheet,{{"Date", type date}, {"Amount", type number}, {"Category", type text}})
   in
       ChangedType

2. Load Budget Data Table

   let
       Source = Excel.Workbook(File.Contents("C:\Path\To\BudgetData.xlsx"), null, true),
       Budget_Sheet = Source{[Name="Budget"]}[Content],
       ChangedType = Table.TransformColumnTypes(Budget_Sheet,{{"Date", type date}, {"Amount", type number}, {"Category", type text}})
   in
       ChangedType

Shaping and Transforming Data

1. Removing Unnecessary Columns

   let
       RemovedColumns = Table.RemoveColumns(ChangedType, {"UnnecessaryColumn1", "UnnecessaryColumn2"})
   in
       RemovedColumns

2. Filtering Data

   let
       FilteredRows = Table.SelectRows(RemovedColumns, each ([Date] >= #date(2023, 1, 1) and [Date] <= #date(2023, 12, 31)))
   in
       FilteredRows

3. Pivoting Data (if needed)

   let
       PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Category]), "Category", "Amount", List.Sum)
   in
       PivotedColumn

4. Renaming Columns

   let
       RenamedColumns = Table.RenameColumns(RemovedColumns, {{"OldColumnName", "NewColumnName"}})
   in
       RenamedColumns

5. Merging Data: Actual vs Budget

   let
       MergedTables = Table.NestedJoin(ActualsTable, {"Date", "Category"}, BudgetTable, {"Date", "Category"}, "BudgetData", JoinKind.LeftOuter),
       ExpandedBudget = Table.ExpandTableColumn(MergedTables, "BudgetData", {"Amount"}, {"BudgetAmount"})
   in
       ExpandedBudget

6. Calculating Variance

   let
       AddedVariance = Table.AddColumn(ExpandedBudget, "Variance", each [Amount] - [BudgetAmount], type number)
   in
       AddedVariance

Final Steps

1. Close & Load After performing all the transformations, click on the 'Close & Load' option in Power Query to load the transformed data back into Excel for further analysis.

By following these steps, you can effectively load and shape your Actual vs Budget data in Excel using Power Query M language, enabling more insightful data analysis and visualization.

Transforming Columns and Rows in Power Query M Language for Actual vs Budget Data

Below you will find the practical implementation for transforming columns and rows in Power Query M Language for an Excel data project comparing actual vs budget data.

Example Data

Assume you have the following initial dataset loaded in Power Query:

Transforming Columns and Rows

Step 1: Unpivot the Actual and Budget Columns

To analyze the difference between actual and budget values, you may want to unpivot these columns.

let
    Source = YourSourceHere,
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Date", "Category"}, "Attribute", "Value")
in
    UnpivotedColumns

This will transform your data to:

Step 2: Pivoting More Granular Rows into Columns

If you want to have a table where each row is a monthly summary for each category, with actuals and budget side-by-side:

let
    Source = YourSourceHere,
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Date", "Category"}, "Attribute", "Value"),
    PivotedColumns = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Attribute]), "Attribute", "Value")
in
    PivotedColumns

This will transform your data to:

Step 3: Calculating Variances

To calculate the variance between actuals and budgets:

let
    Source = YourSourceHere,
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Date", "Category"}, "Attribute", "Value"),
    PivotedColumns = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Attribute]), "Attribute", "Value"),
    AddVarianceColumn = Table.AddColumn(PivotedColumns, "Variance", each [Actual] - [Budget])
in
    AddVarianceColumn

This adds a "Variance" column to your dataset:

Conclusion

By following the above steps, you have successfully transformed your data to allow for better insights and analysis. The transformations include unpivoting, pivoting, and calculating variance—all essential for a thorough understanding of actual vs budget data in Excel using Power Query M Language.

Implementing M Code for Data Transformation

In this part, we will focus on transforming and shaping the actual vs budget data in Power Query using the M language. The objective here is to demonstrate how to perform typical transformations such as filtering, merging, and aggregating data to provide meaningful insights.

Start with Initial Transformations

Filtering Data

We want to filter the data to show only entries from the current financial year.

let
    Source = Excel.CurrentWorkbook(){[Name="ActualBudgetData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Year] = Date.Year(DateTime.LocalNow()))
in
    FilteredRows

Renaming Columns

Standardize column names for better readability.

let
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"Actual Amount", "Actual"}, {"Budget Amount", "Budget"}})
in
    RenamedColumns

Merging Data

Assuming that ActualData and BudgetData are in different tables, we can merge them.

let
    ActualData = Excel.CurrentWorkbook(){[Name="ActualData"]}[Content],
    BudgetData = Excel.CurrentWorkbook(){[Name="BudgetData"]}[Content],
    MergedTable = Table.NestedJoin(ActualData, {"ID"}, BudgetData, {"ID"}, "BudgetTable", JoinKind.Inner),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "BudgetTable", {"Budget"}, {"Budget"})
in
    ExpandedTable

Creating Custom Columns

Calculate the variance between actual and budget amounts.

let
    CustomColumn = Table.AddColumn(ExpandedTable, "Variance", each [Actual] - [Budget])
in
    CustomColumn

Aggregating Data

Group by necessary categories and summarize actual and budget data.

let
    GroupedTable = Table.Group(CustomColumn, {"Category"}, {{"TotalActual", each List.Sum([Actual]), type number}, {"TotalBudget", each List.Sum([Budget]), type number}})
in
    GroupedTable

Adding More Insights

Calculate percentage variance:

let
    PercentVariance = Table.AddColumn(GroupedTable, "PercentVariance", each ([TotalActual] - [TotalBudget]) / [TotalBudget] * 100, type number)
in
    PercentVariance

Final Result

Combine all steps in a final query:

let
    Source = Excel.CurrentWorkbook(){[Name="ActualBudgetData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Year] = Date.Year(DateTime.LocalNow())),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"Actual Amount", "Actual"}, {"Budget Amount", "Budget"}}),
    ActualData = Excel.CurrentWorkbook(){[Name="ActualData"]}[Content],
    BudgetData = Excel.CurrentWorkbook(){[Name="BudgetData"]}[Content],
    MergedTable = Table.NestedJoin(ActualData, {"ID"}, BudgetData, {"ID"}, "BudgetTable", JoinKind.Inner),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "BudgetTable", {"Budget"}, {"Budget"}),
    CustomColumn = Table.AddColumn(ExpandedTable, "Variance", each [Actual] - [Budget]),
    GroupedTable = Table.Group(CustomColumn, {"Category"}, {{"TotalActual", each List.Sum([Actual]), type number}, {"TotalBudget", each List.Sum([Budget]), type number}}),
    PercentVariance = Table.AddColumn(GroupedTable, "PercentVariance", each ([TotalActual] - [TotalBudget]) / [TotalBudget] * 100, type number)
in
    PercentVariance

Conclusion

This implementation leverages Power Query's M language to transform actual vs budget data effectively. The steps include filtering by year, renaming columns, merging tables, calculating variances, grouping data, and adding insightful custom columns. Apply these transformations to streamline your data analysis workflows in Excel.

Validating and Utilizing Transformed Data

In this section, we will validate and utilize the transformed data within Power Query to ensure the accuracy and usability of the transformed Actual vs Budget data. This guide assumes transformations have already been applied and focuses on confirming their correctness and preparing the transformed data for further analysis.

Steps to Validate and Utilize Transformed Data

Step 1: Validate Transformed Data

1. Check for Data Integrity:

   • Ensure that no data values have been lost or altered unexpectedly during the transformation process.
   • Use the Table.RowCount function to verify that the number of rows matches expectations.

   let
       Source = /* Your Data Source */,
       TransformedData = /* Your Transformation Steps */,
       RowCount = Table.RowCount(TransformedData)
   in
       RowCount

2. Confirm Data Types:

   • Explicitly specify data types for each column to prevent any issues during data analysis.

   let
       Source = /* Your Data Source */,
       TransformedData = /* Your Transformation Steps */,
       TypedData = Table.TransformColumnTypes(TransformedData, {
           {"Actual", type number},
           {"Budget", type number},
           {"Date", type date}
       })
   in
       TypedData

3. Verify Data Consistency:

   • Ensure that transformed data follows the expected format for analysis.
   • Use Power Query’s built-in profiling tools to examine column distributions and identify any anomalies (like missing values).

Step 2: Utilizing the Transformed Data

1. Create Calculated Columns:

   • Add new columns to derive meaningful insights from the transformed data, such as calculating variance between actual and budget values.

   let
       Source = /* Your Data Source */,
       TransformedData = /* Your Transformation Steps */,
       TypedData = Table.TransformColumnTypes(TransformedData, {
           {"Actual", type number},
           {"Budget", type number},
           {"Date", type date}
       }),
       AddVarianceColumn = Table.AddColumn(TypedData, "Variance", each [Actual] - [Budget], type number)
   in
       AddVarianceColumn

2. Aggregate Data for Analysis:

   • Group data by relevant categories (e.g., by month or department) to summarize Actual vs Budget differences.

   let
       Source = /* Your Data Source */,
       TransformedData = /* Your Transformation Steps */,
       TypedData = Table.TransformColumnTypes(TransformedData, {
           {"Actual", type number},
           {"Budget", type number},
           {"Date", type date}
       }),
       AddVarianceColumn = Table.AddColumn(TypedData, "Variance", each [Actual] - [Budget], type number),
       GroupedData = Table.Group(AddVarianceColumn, {"Date"}, {
           {"TotalActual", each List.Sum([Actual]), type number},
           {"TotalBudget", each List.Sum([Budget]), type number},
           {"TotalVariance", each List.Sum([Variance]), type number}
       })
   in
       GroupedData

3. Prepare Data for Visualization:

   • Ensure data is in a clean, aggregated state, ready to be loaded into Excel or other visualization tools.

   let
       Source = /* Your Data Source */,
       TransformedData = /* Your Transformation Steps */,
       TypedData = Table.TransformColumnTypes(TransformedData, {
           {"Actual", type number},
           {"Budget", type number},
           {"Date", type date}
       }),
       AddVarianceColumn = Table.AddColumn(TypedData, "Variance", each [Actual] - [Budget], type number),
       GroupedData = Table.Group(AddVarianceColumn, {"Date"}, {
           {"TotalActual", each List.Sum([Actual]), type number},
           {"TotalBudget", each List.Sum([Budget]), type number},
           {"TotalVariance", each List.Sum([Variance]), type number}
       }),
       CleanedData = Table.SelectColumns(GroupedData, {"Date", "TotalActual", "TotalBudget", "TotalVariance"})
   in
       CleanedData

Outcome

Following these steps will help you validate the transformed Actual vs Budget data and prepare it for further analysis. By adding calculated columns, aggregating data, and ensuring data integrity and consistency, you can gain more accurate and actionable insights from your analysis.