How to Construct a Table in Power Query using Lists and Records

How to Define a Table Using #table with Lists and Records in Power Query
In Power BI, you can define tables manually using the #table function in Power Query. This is useful when you want to build sample data, test logic, or create quick examples without loading data from external sources. By combining lists and records, you can structure your table exactly how you need it.

In this article we will discuss various methods to create custom tables using #table with lists and records.

Method 1: Create Table using Columns with Lists:

let

    Source = Table.TransformColumnTypes(

        #table(

            {"TextColumn", "NumberColumn", "DateColumn", "DateTimeColumn", "LogicalColumn", "DurationColumn", "TimeColumn", "BinaryColumn"},

            {

                {"Alpha", 100, #date(2025, 10, 1), #datetime(2025, 10, 1, 10, 0, 0), true, #duration(1, 2, 30, 45), #time(10, 0, 0), #binary({65,66})},

                {"Beta", 200.5, #date(2025, 10, 2), #datetime(2025, 10, 2, 11, 15, 0), false, #duration(0, 5, 0, 0), #time(11, 15, 0), #binary({67,68})},

                {"Gamma", -50, #date(2025, 10, 3), #datetime(2025, 10, 3, 9, 45, 0), true, #duration(2, 0, 0, 0), #time(9, 45, 0), #binary({69,70})}

            }

        ),

        {

            {"TextColumn", type text},

            {"NumberColumn", type number},

            {"DateColumn", type date},

            {"DateTimeColumn", type datetime},

            {"LogicalColumn", type logical},

            {"DurationColumn", type duration},

            {"TimeColumn", type time},

            {"BinaryColumn", type binary}

        }

    )

in

    Source

Result:

-----------------------------------------------

Method 2: Create Table using Rows with Records:

let

    Row1 = [TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1), DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0), LogicalColumn = true, DurationColumn = #duration(1, 2, 30, 45), TimeColumn = #time(10, 0, 0), BinaryColumn = #binary({65,66})],

    Row2 = [TextColumn = "Beta", NumberColumn = 200.5, DateColumn = #date(2025, 10, 2), DateTimeColumn = #datetime(2025, 10, 2, 11, 15, 0), LogicalColumn = false, DurationColumn = #duration(0, 5, 0, 0), TimeColumn = #time(11, 15, 0), BinaryColumn = #binary({67,68})],

    Row3 = [TextColumn = "Gamma", NumberColumn = -50, DateColumn = #date(2025, 10, 3), DateTimeColumn = #datetime(2025, 10, 3, 9, 45, 0), LogicalColumn = true, DurationColumn = #duration(2, 0, 0, 0), TimeColumn = #time(9, 45, 0), BinaryColumn = #binary({69,70})],

   

    Source = Table.FromRecords({Row1, Row2, Row3}),

    SourceWithType = Table.TransformColumnTypes(Source, {

        {"TextColumn", type text},

        {"NumberColumn", type number},

        {"DateColumn", type date},

        {"DateTimeColumn", type datetime},

        {"LogicalColumn", type logical},

        {"DurationColumn", type duration},

        {"TimeColumn", type time},

        {"BinaryColumn", type binary}

    })

in

    SourceWithType

Result:

-----------------------------------------------

Method 3: Create Table using Lists and Records:

let

    _Names = {"Alpha", "Beta", "Gamma"},

    _Indexed = List.Zip({_Names, {1..List.Count(_Names)}}),

    _Rows = List.Transform(_Indexed, each [

        TextColumn = _{0},

        NumberColumn = _{1} * 100,

        DateColumn = #date(2025, 10, 1),

        DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0),

        LogicalColumn = true,

        DurationColumn = #duration(0, 1, 0, 0),

        TimeColumn = #time(10, 0, 0),

        BinaryColumn = #binary({65,66})

    ]),

    table = Table.FromRecords(_Rows)

in

    table

-----------------------OR------------------------

let

    _Names = {"Alpha", "Beta", "Gamma"},

    _Rows = List.Transform(_Names, each [

        TextColumn = _,

        NumberColumn = (List.PositionOf(_Names, _) + 1) * 100,

        DateColumn = #date(2025, 10, 1),

        DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0),

        LogicalColumn = true,

        DurationColumn = #duration(0, 1, 0, 0),

        TimeColumn = #time(10, 0, 0),

        BinaryColumn = #binary({65,66})

    ]),

    table = Table.FromRecords(_Rows)

in

    table

Result:

-----------------------------------------------

Method 4: Create Table using List.Combine method:

let

    GroupA = {

        [TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1)],

        [TextColumn = "Beta", NumberColumn = 200, DateColumn = #date(2025, 10, 2)]

    },

    GroupB = {

        [TextColumn = "Gamma", NumberColumn = 300, DateColumn = #date(2025, 10, 3)],

        [TextColumn = "Delta", NumberColumn = 400, DateColumn = #date(2025, 10, 4)]

    },

    AllRows = List.Combine({GroupA, GroupB}),

    RawTable = Table.FromRecords(AllRows),

    TableWithType = Table.TransformColumnTypes(RawTable, {

        {"TextColumn", type text},

        {"NumberColumn", type number},

        {"DateColumn", type date}

    })

in

    TableWithType

Result:

-----------------------------------------------

Method 5: Create Table using Table.Combine method:

let

    TableA = Table.FromRecords({

        [TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1)],

        [TextColumn = "Beta", NumberColumn = 200, DateColumn = #date(2025, 10, 2)]

    }),

    TableB = Table.FromRecords({

        [TextColumn = "Gamma", NumberColumn = 300, DateColumn = #date(2025, 10, 3)],

        [TextColumn = "Delta", NumberColumn = 400, DateColumn = #date(2025, 10, 4)]

    }),

    CombinedTable = Table.Combine({TableA, TableB}),

    TableWithType = Table.TransformColumnTypes(CombinedTable, {

        {"TextColumn", type text},

        {"NumberColumn", type number},

        {"DateColumn", type date}

    })

in

    TableWithType

Result:

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------

How to Run a Stored Procedure with Parameters using a Function in Power Query

How to Execute a Stored Procedure with Parameters using a Function in Power Query

Scenario:
Lets say there is a Stored Procedure sp_SalesByProduct with parameters as @ProductName, @StartDate and @EndDate, defined in the SQL Server.

Now we want to run this Stored Procedure in Power Query, by dynamically passing the list of values for the @ProductName and dynamic datetime values for @StartDate and @EndDate. 

Phase 1: Define a Function with Dynamic Parameter Values:

We can achieve above Scenario by Creating a function in Power Query, by passing the below code in a new Blank Query and then give the function name as "fn_SalesByProduct":

let

    /* Define a function that calls the stored procedure sp_SalesByProduct */

    fn_SalesByProduct = (ProductName as text, StartDate as datetime, EndDate as datetime) =>

    let

        /* Format parameters for SQL Server */

        _StartDateVal = "'" & DateTime.ToText(StartDate, "yyyy-MM-dd HH:mm:ss") & "'",

        _EndDateVal = "'" & DateTime.ToText(EndDate, "yyyy-MM-dd HH:mm:ss") & "'",

        _ProductName = "'" & Text.Trim(ProductName) & "'",

        /* Construct EXEC statement for Stored Procedure */

        _Src_Qry = 

            "EXEC " & p_schema & ".sp_SalesByProduct " & 

            "@ProductName = " & _ProductName & ", " & 

            "@StartDate = " & _StartDateVal & ", " & 

            "@EndDate = " & _EndDateVal,

        /* Execute the stored procedure against the SQL Server Database */

        Source = Sql.Database(p_server, p_database, [Query = _Src_Qry])

    in

        Source

in

    fn_SalesByProduct

Notes:
The Power Query parameters p_server , p_database, p_schema, will hold the values for the Server, Database and Schema names of the SQL Server data source.

The values for the function parameters ProductName, StartDate, EndDate will be passed from the below step in the Invoke Function Query.

Phase 2: Invoke the Function to generate the Dataset with Results:

Now we will invoke the above function to generate a dataset fact_SalesByProduct with results using the below Code:

fact_SalesByProduct:

let

    _TodayDT = DateTime.Date(DateTime.LocalNow()),

    _StartDT =  DateTime.From(Date.StartOfMonth(Date.AddMonths(_TodayDT, -1))),

    _EndDT =  DateTime.From(Date.StartOfMonth(Date.AddMonths(_TodayDT, 0))),

    

/* List of Product Names to be used in the query to run by Function */

    _List_ProductName = { "Radio", "Television", "Laptop" },

    /* Invoke the fn_SalesByProduct function for each value in the Products List */

    Function_Result = List.Transform(

        _List_ProductName,

        each try fn_SalesByProduct(_, _StartDT, _EndDT) otherwise #table({}, {})

    ),

    /* Combine the Function results for each Product into a single table */

    Combined_Result = Table.Combine(Function_Result)

in

    Combined_Result

Notes:
The values for the function parameters ProductName, StartDate, EndDate are dynamically defined with in this Invoke Query and will be pass back to the function:
fn_SalesByProduct(_, _StartDT, _EndDT)

This function will run for each (_) ProductName will be passed from _List_ProductName.

Please note: This may not be a better approach when we have a huge list of values to pass to the procedure. In this approach, for each parameter value, you may need to allow the permission to run the Native query.

You can try to automate this process from backed SQL Server to generate a View or a table with the required data for reporting.

However, you can use above logic just for reference for another scenarios, to pass Parameter values (Single Values instead of List of Values) dynamically to run a native SQL Query.

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------

How to use meta keyword in Power Query to store metadata from a query step

How to use Value.Metadata function in Power Query to reference a Step from another Query

In Power Query (M language), the meta keyword is used to attach metadata from a step to a variable. This metadata serves as descriptive information that can be referenced in another queries using functions like Value.Metadata(), but it does not alter the underlying value itself.

Scenario:

Suppose if we have a query with various transformation steps like shown below. From which we want to store the metadata of the steps ChangeType and GroupByCountryProd into the variables for later use in a reference query.

We can achieve this Scenario by using the meta keyword at the last step of the query to store the metadata of required steps.

/* Storing the Metadata of required steps */

meta [ref_ChangeType = ChangeType, ref_GroupByCountryProd = GroupByCountryProd]

The complete Power Query of fact_Orders_Sample is as follows:

let

    Source = Excel.Workbook(File.Contents(SrcPath), null, true),

    fct_OrderDetails_Sheet = Source{[Item="fct_OrderDetails",Kind="Sheet"]}[Data],

    PromoteHeaders = Table.PromoteHeaders(fct_OrderDetails_Sheet, [PromoteAllScalars=true]),

    ChangeType = Table.TransformColumnTypes(PromoteHeaders,{{"Order_ID", Int64.Type}, 

    {"Order_Date", type date}, {"Customer_ID", Int64.Type}, {"Product_ID", Int64.Type}, {"Country_ID", Int64.Type}, {"Gross_Sales", Int64.Type}}),

    GroupByCountryProd = Table.Group(ChangeType, {"Country_ID", "Product_ID"}, {{"Total Sales", each List.Sum([Gross_Sales]), type nullable number}}),

    GroupByProduct = Table.Group(GroupByCountryProd, {"Product_ID"}, {{"Max_Sales", each List.Max([Total Sales]), type nullable number}}) 

    /* Storing the Metadata of required steps */

    meta [ref_ChangeType = ChangeType, ref_GroupByCountryProd = GroupByCountryProd]

in

    GroupByProduct

Now we can create a referenced query using the meta data from any of the above steps that defined in the meta function in the above base query.

ref_Orders_Sample:
= Value.Metadata(fact_Orders_Sample)

Now we can reference and use the above metadata to create a referenced query based on the meta variable ref_ChangeType as shown below:

ref_Orders_Sample:

let

    Source = Value.Metadata(fact_Orders_Sample)[ref_ChangeType]

in

    Source

Result:

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------