Power Query Function to Expand a Table

Overview

This document outlines a Power Query (M) function designed to expand a table regardless of varying header names. The function takes a table and a list of columns to expand, allowing users to handle data dynamically.

Function: ExpandTable

Purpose

To expand specified columns in a table and return a new table with the expanded data. The function is flexible enough to handle different header names.

Parameters

• inputTable (table): The table to be expanded.
• columnsToExpand (list): A list of column names (strings) that need to be expanded.

Return Type

• (table): A new table with the specified columns expanded.

Exceptions

• Throws an error if inputTable is not a table or if columnsToExpand is not a list.
• Throws an error if any specified column does not exist in inputTable.

// Define the ExpandTable function.
let
    ExpandTable = (inputTable as table, columnsToExpand as list) as table =>
    // Documentation block
    let
        // Validate the input parameters
        _ = if not Table.IsTable(inputTable) then error "Input must be a table",
        _ = if not List.IsList(columnsToExpand) then error "Columns to expand must be a list",
        
        // Check for invalid column names
        validColumns = List.Select(columnsToExpand, each List.Contains(Table.ColumnNames(inputTable), _)),
        invalidColumns = List.Difference(columnsToExpand, validColumns),
        _ = if List.Count(invalidColumns) > 0 then error "Invalid columns: " & Text.Join(invalidColumns, ", "),
        
        // Expand the specified columns
        expandedTable = Table.ExpandRecordColumn(inputTable, validColumns, List.Transform(validColumns, each {_, _}))
    in
        // Return the expanded table
        expandedTable
in
    ExpandTable

Code Explanation

1. Function Definition: The ExpandTable function is defined using the let expression, which allows the definition of variables and logic for the function.

2. Input Validation:

   • Checks if inputTable is a valid table using Table.IsTable.
   • Checks if columnsToExpand is a list using List.IsList.
   • Validates that all specified columns exist in the table and identifies any invalid columns.

3. Expand Columns:

   • Uses Table.ExpandRecordColumn, allowing the expansion of specified columns. The transformation of column names ensures that the function handles each column correctly.

4. Return Result: Finally, the function returns the expanded table.

Example Usage

Suppose you have the following table:

You can call the ExpandTable function to expand the Details column:

let
    Source = Table.FromRecords({
        [ID=1, Details=[Name="Alice", Age=30]],
        [ID=2, Details=[Name="Bob", Age=25]]
    }),
    columnsToExpand = {"Details"}, // Column to expand
    ExpandedTable = ExpandTable(Source, columnsToExpand)
in
    ExpandedTable

Resulting Table

After applying the ExpandTable function, the result will be:

Conclusion

This Power Query function provides a robust solution for expanding tables with varying header names while validating inputs appropriately. For further learning on Power Query and related techniques, consider exploring resources available through the Enterprise DNA Platform.