Creating a Table in M (Power Query)

To create a table in M (Power Query), you can follow these steps:

1. Define the function:

   • The function will take a list of column names and a list of data rows as parameters.
   • It will return a table.

2. Validate the inputs:

   • Ensure that the list of column names is not empty.
   • Ensure that the list of data rows is not empty.
   • Ensure that all data rows have the same number of columns as the list of column names.

3. Create the column definitions:

   • Iterate over the list of column names and create a record for each column definition using the #table function.

4. Create the rows of data:

   • Iterate over the list of data rows and create records using the Record.FromList function. Each record will have field names corresponding to the column names.

5. Combine the column definitions and data rows:

   • Use the Table.Combine function to combine the column definitions and data rows into a single table.

Here's the code that implements these steps:

let
    CreateTable = (columnNames as list, dataRows as list) as table =>
        let
            // Step 2: Validate inputs
            columnCount = List.Count(columnNames),
            rowCount = List.Count(dataRows),
            validInput = columnCount > 0
                          and rowCount > 0
                          and List.Count(List.First(dataRows)) = columnCount,
        
            // Step 3: Create column definitions
            columnDefinitions = List.Transform(columnNames, each #table(1, {{_}})),
        
            // Step 4: Create data rows
            tableRows = List.Transform(dataRows, each Record.FromList(_, columnNames)),
        
            // Step 5: Combine column definitions and data rows
            result = if validInput then
                         Table.Combine({columnDefinitions, tableRows})
                     else
                         error "Invalid input"
        in
            result
in
    CreateTable

Example usage:

let
    columnNames = {"Name", "Age", "City"},
    dataRows = {{"John", 30, "New York"}, {"Jane", 25, "London"}},
    table = CreateTable(columnNames, dataRows)
in
    table

Output:

| Name | Age | City     |
|------|-----|----------|
| John | 30  | New York |
| Jane | 25  | London   |

This code defines a function called CreateTable that takes a list of column names and a list of data rows as parameters. It performs validation, creates the column definitions and data rows, and combines them into a table. The function can be used to create a table by passing the column names and data rows as arguments.