Forum Discussion

Manjarigoyal1's avatar
Manjarigoyal1
Frequent Visitor
2 years ago
Solved

How to substract current row with next row and then substract the result to next row?

I am trying to substarct the row1 to row2 and then the result will be substracted from row 3 and so on. I am looking for M Query NOT in DAX. Below is the sample data with expected result. Thanks for ...
Need Help
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    2 years ago

    there are so many running total solutions out there... 

    let
    lst = List.Buffer(Table.ToRows(your_table)),
    res = Table.FromList(
        List.Generate(
            () => [i = 0, c = lst{0},  s = c{1}],
            (x) => x[i] < List.Count(lst),
            (x) => [i = x[i] + 1, c = lst{i}, s = x[s] - c{1}],
            (x) => x[c] & {x[s]}
        ),
        (x) => x, 
        {"week", "delivery", "expected"}
    )
in
    res
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

there are so many running total solutions out there... 

let
    lst = List.Buffer(Table.ToRows(your_table)),
    res = Table.FromList(
        List.Generate(
            () => [i = 0, c = lst{0},  s = c{1}],
            (x) => x[i] < List.Count(lst),
            (x) => [i = x[i] + 1, c = lst{i}, s = x[s] - c{1}],
            (x) => x[c] & {x[s]}
        ),
        (x) => x, 
        {"week", "delivery", "expected"}
    )
in
    res