Realize circular dependency columns using M function

[v-tangjie-msft]

Scenario: 

In this article, we would like to realize circular dependency columns using M function “List.Accumulate” in Power Query.

Expected Result:

We need to get three columns: Begin, Export and Ending.

They are calculated like this:

Begin = Previous Ending

Export = ( Begin + Import ) / 2

Ending = Begin + Import - Export

Sample Data:

In the sample data, there is one table:

We have a starting value = 12 when Index = 0, and starting with Index = 1, there will be corresponding input values Import.

How:

When we try to solve this problem using the regular “Table.Addcolumn” and “List.Transform”, we find that it doesn't work.

For example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQIiQyOlWJ1oJUMQ0xgkBOIZgXiGMB5I2NAExgMxDE3BvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Import = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Import", Int64.Type}, {"Value", Int64.Type}}),
    AddedBegin = Table.AddColumn(#"Changed Type", "Begin", each null, type number),
    BeginFilled = Table.FromRecords(List.Transform({0..Table.RowCount(AddedBegin)-1}, each Record.TransformFields(AddedBegin{_},{"Begin", (B) => if _ = 0 then null else AddedBegin{_ - 1} [Ending]}))),
    AddedExport = Table.AddColumn(BeginFilled, "Export", each [Begin] + [Import]/2, type number),
    AddedEnding = Table.AddColumn(AddedExport, "Ending", each if [Index] = 0 then [Value] else [Begin] + [Import] - [Export], type number)
in
    AddedEnding

If we use this M function, we will get these error information in all columns:

That's because in this case, each time you add a column, you're referencing an entire column of data, but we don't have any of the columns that are complete before the data conversion, so Power Query can't find the data in the corresponding column.

In Power Query there is such a function: “List.Accumulate”, which can achieve the desired result. Its role is to accumulate a summary value from the items in the list.
 

First, we use “List.Accumulate” to create the Begin, Export, Ending columns. I would use comments to explain what each line accomplishes:

// Use List.Accumulate to create Begin, Export, Ending columns
 AccumulateColumns = List.Accumulate(
        List.Zip({ChangedType[Index], ChangedType[Import]}), 
        {[Index=0, Begin=null, Export=null, Ending=InitialEnding]},
        (state, current) => 
        Let
             // Unwrap the current line's Index and Import
            CurrentIndex = current{0},
            CurrentImport = current{1},
             // Calculate the Ending of the previous line as the Begin of the current line
            PreviousEnding = if CurrentIndex = 0 then null else state{CurrentIndex}[Ending],
             // Calculate the Export column
            CurrentExport = if CurrentIndex = 0 then null else (PreviousEnding + CurrentImport) / 2,
             // Calculate the Ending column
            CurrentEnding = if CurrentIndex = 0 then InitialEnding else PreviousEnding + CurrentImport - CurrentExport,
             // Construct the current row record and append it to the status list
            CurrentRecord = [Index=CurrentIndex, Begin=PreviousEnding, Export=CurrentExport, Ending= CurrentEnding]
        in
            state & {CurrentRecord}
    ),

At this point you can see that we get a Record column:

Let's tap the Record away:
 

We find that each Record contains the Begin, Export, and Ending data that should be returned for each loop. This is what "List.Accumulate" does, it only references a single piece of data at a time for the current loop, not a whole column at a time!

We then create a new table containing the accumulated column data:

 AccumulatedTable = Table.FromRecords(AccumulateColumns),

Combining original and cumulative table data:

 JoinedTable = Table.NestedJoin(ChangedType,{"Index"},AccumulatedTable,{"Index"},"NewColumns",JoinKind.LeftOuter),

Expand cumulative column data to a new column:

ExpandedTable = Table.ExpandTableColumn(JoinedTable, "NewColumns", {"Begin", "Export", "Ending"}),

Setting up the final output table:

 FinalTable = ExpandedTable,

Then we can get the desired result:

I've attached the complete code from Advanced Editor below for your reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQIiQyOlWJ1oJUMQ0xgkBOIZgXiGMB5I2NAExgMxDE3BvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Import = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Import", Int64.Type}, {"Value", Int64.Type}}),
    InitialEnding = 12,
    AccumulateColumns = List.Accumulate(
        List.Zip({ChangedType[Index], ChangedType[Import]}),
        {[Index=0, Begin=null, Export=null, Ending=InitialEnding]},
        (state, current) =>
        let
            CurrentIndex = current{0},
            CurrentImport = current{1},
            PreviousEnding = if CurrentIndex = 0 then null else state{CurrentIndex}[Ending],
            CurrentExport = if CurrentIndex = 0 then null else (PreviousEnding + CurrentImport) / 2,
            CurrentEnding = if CurrentIndex = 0 then InitialEnding else PreviousEnding + CurrentImport - CurrentExport,
            CurrentRecord = [Index=CurrentIndex, Begin=PreviousEnding, Export=CurrentExport, Ending= CurrentEnding]
        in
            state & {CurrentRecord}
    ),
    AccumulatedTable = Table.FromRecords(AccumulateColumns),
    JoinedTable = Table.NestedJoin(ChangedType,{"Index"},AccumulatedTable,{"Index"},"NewColumns",JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(JoinedTable, "NewColumns", {"Begin", "Export", "Ending"}),
    FinalTable = ExpandedTable,
    #"Removed Top Rows" = Table.Skip(FinalTable,1)
in
    #"Removed Top Rows"

But I must give a reminder: it is better to prepare the data in Source side. If it has to be processed in Power BI, you may need to consider resource consumption in real practice as doing the same thing at source end using script might consume less CPU/Memory resource. Also Power BI model usually have schedule refresh configured, those kind of design might be not suitable in this scenario and it won’t necessary to calculate all those tables for each refresh if source data does not change.

Summary: 

So, when we encounter Circular Dependency scenario again, we can use "List.Accumulate" to solve the current problem.

Hope this article will help people with similar questions!
 

Document reference:

List.Accumulate - PowerQuery M | Microsoft Learn

Author: Junyan Tao

Reviewer: Ula and Kerry

Realize circular dependency using M function.pbix