Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to implement this formula despite the self-referencing/circular logic?

I have a table of data in Power Query and I want to create a column, called "Current Zinc". It will do the following:

- if [Index] = 0, then it will take the value of "ComponentTankZinc" 

- if ComponentTankZinc" <> Table{[Index]-1}["ComponentTankZinc" (i.e. value of "ComponentTankZinc" in this row differs from the value in the previous row, then it will take the value of "ComponentTankZinc" of the current row

-else, it will take on a value based on this formula: 

"Current Zinc" = ("IncomingZinc" * "LevelDifference" + Table{[Index]-1}"Current Zinc" * "StartTankLevel")/"Level" 

 

The problem is that the formula defines the current row's value in terms of the value of the same column in the previous row. This cannot be done in Powery Query. But is there a way to circumvent this? Is it possible to redesign my calculation philosophy? 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    With the help of copilot, I have generated a solution using list.accumulate:

     

    let
    Source = EventFrames,
    #"Sorted Rows" = Table.Sort(Source,{{"Event Frame Start Time", Order.Descending}}),
    AddIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    // Convert Table to List of Records
    ListRecords = Table.ToRecords(AddIndex),
    // Accumulate the "Current Zinc" values
    AccumulatedList = List.Accumulate(
        ListRecords,
        {},
        (state, current) =>
            let
                // Get the previous record if it exists
                prev = if List.Count(state) = 0 then null else List.Last(state),
                // Calculate the "CurrentZinc" value
                currentZinc = if current[Index] = 0 then
                                current[ComponentTankZinc]
                              else if prev[ComponentTankZinc] <> current[ComponentTankZinc] then
                                current[ComponentTankZinc]
                              else
                                (current[IncomingZinc] * current[LevelDifference] + prev[CurrentZinc] * current[StartTankLevel]) / current[Level],
                // Add the "Current Zinc" value to the current record
                newRecord = Record.AddField(current, "CurrentZinc", currentZinc)
            in
                state & {newRecord}
    ),
    // Convert the list back to a table
    ResultTable = Table.FromRecords(AccumulatedList),
    #"Reordered Columns" = Table.ReorderColumns(ResultTable,{"Id", "Component Tank Level Increase", "Event Frame Start Time", "Event Frame End Time", "Event Frame Duration", "Level", "ComponentTankZinc", "IncomingZinc", "CurrentZinc", "TimeStamp", "LevelDifference", "StartTankLevel", "PIIntTSTicks", "PIIntShapeID", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"CurrentZinc", type number}, {"Event Frame Start Time", type datetime}})
in
    #"Changed Type"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    With the help of copilot, I have generated a solution using list.accumulate:

     

    let
    Source = EventFrames,
    #"Sorted Rows" = Table.Sort(Source,{{"Event Frame Start Time", Order.Descending}}),
    AddIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    // Convert Table to List of Records
    ListRecords = Table.ToRecords(AddIndex),
    // Accumulate the "Current Zinc" values
    AccumulatedList = List.Accumulate(
        ListRecords,
        {},
        (state, current) =>
            let
                // Get the previous record if it exists
                prev = if List.Count(state) = 0 then null else List.Last(state),
                // Calculate the "CurrentZinc" value
                currentZinc = if current[Index] = 0 then
                                current[ComponentTankZinc]
                              else if prev[ComponentTankZinc] <> current[ComponentTankZinc] then
                                current[ComponentTankZinc]
                              else
                                (current[IncomingZinc] * current[LevelDifference] + prev[CurrentZinc] * current[StartTankLevel]) / current[Level],
                // Add the "Current Zinc" value to the current record
                newRecord = Record.AddField(current, "CurrentZinc", currentZinc)
            in
                state & {newRecord}
    ),
    // Convert the list back to a table
    ResultTable = Table.FromRecords(AccumulatedList),
    #"Reordered Columns" = Table.ReorderColumns(ResultTable,{"Id", "Component Tank Level Increase", "Event Frame Start Time", "Event Frame End Time", "Event Frame Duration", "Level", "ComponentTankZinc", "IncomingZinc", "CurrentZinc", "TimeStamp", "LevelDifference", "StartTankLevel", "PIIntTSTicks", "PIIntShapeID", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"CurrentZinc", type number}, {"Event Frame Start Time", type datetime}})
in
    #"Changed Type"