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

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