Value.Lineage

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Value.Lineage

What is Value.Lineage?

Value.Lineage is a M function in Power Query that returns a table containing the lineage of a particular value within a column or table. The lineage shows the path that the value took from its original source to its current location. This can be useful when you are working with large datasets and need to trace the origin of a particular value.

How to Use Value.Lineage

To use Value.Lineage, you first need to select the column or table that contains the value you want to trace. Then, you need to call the Value.Lineage function and pass in the value you want to trace as a parameter. The function will then return a table containing the lineage of the value.

Here is an example of how to use Value.Lineage:

let

Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

ValueToTrace = "500",

Lineage = Value.Lineage(Source[Amount], ValueToTrace)

in

Lineage

In this example, we are selecting the Sales table from the current workbook and passing in the value “500” as the value to trace. We then call the Value.Lineage function and pass in the Sales[Amount] column as the source column. The function will return a table containing the lineage of the value “500” within the Sales[Amount] column.

The M Code Behind Value.Lineage

Now that we know how to use Value.Lineage, let’s take a closer look at the M code behind the function. The M code for Value.Lineage is as follows:

let

ValueLineage = (Source as list, Value as any) =>

let

GetLineage = (CurrentList as list, Value as any) =>

List.Accumulate(CurrentList, {[Value = Value, Lineage = {}]}, (state, current) =>

if current[Value] = Value then

state & {[Value = current[Value], Lineage = state{List.Count(state)}[Lineage] & {current}}}

else if Type.Is(current[Value], type table) then

state & {[Value = current[Value], Lineage = state{List.Count(state)}[Lineage] & {GetLineage(current[Value], Value)}}}

else

state

),

Result = GetLineage(Source, Value)

in

Table.FromRecords(Result{0}[Lineage])

in

ValueLineage

The ValueLineage function takes two parameters: Source and Value. Source is the column or table containing the value to trace, and Value is the value to trace. The function then calls the GetLineage function to recursively traverse the table or column to find the lineage of the value.

The GetLineage function takes two parameters: CurrentList and Value. CurrentList is the current list being traversed, and Value is the value to trace. The function then uses the List.Accumulate function to iterate over the list and build a new list containing the lineage of the value.

If the current item in the list matches the value being traced, the function adds the current item to the lineage list. If the current item is a table, the function recursively calls itself to traverse the table and find the lineage of the value.

The Result of the GetLineage function is then passed to the Table.FromRecords function, which returns a table containing the lineage of the value.

Value.Lineage is a powerful M function in Microsoft Power Query that can be used to trace the source of a particular value within a table or column. By understanding the M code behind the function, you can customize and extend its functionality to meet your specific data analysis needs. The next time you need to trace the origin of a value within a large dataset, consider using Value.Lineage to make your analysis more effective.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)