Power Query : create a new column based on nearest value that meets the condition

[harivardhan]

Hello, Thanks in advance,
The table is is as below:



In the table, I want to create a column "result" based on first three columns using POWER QUERY/

"Result" = (Quantity) * ("value in "result" that is corresponding to nearest above row that satisfies the condition ["Heirachy" of the row = "Heirachy" of the current row -1])


example: row 8:
1. formula should check all the rows above row 8 for condition
2. "heirachy" of row 8 = 3
row 7 and row 3 satisfy the condition, since their "heirachy" is (3-1=2)
3.but the nearest one is row 7, so we will take the "result" of row 7 and multiply with "Quantity" of row 8 to get the "result of row 8

example : row 14:

1. row 4, 5, 7, 8, 9, 10, 11, 13 satisfy the condition.
2. nearest is row 13. so we multiply result of row 13 and quantity of row 14 to get the result of row 14.

 

[p45cal]

Very easy in Excel, in D2:
=XLOOKUP(A2-1,A1:A$2,D1:D$2,1,,-1)*C2
copied down, but haven't been able to do it in Power Query yet!

 

[harivardhan]

The data I have given is just sample..I will using this logic for very large dynamic data.....so i need this in power query.

 

[H.M]

Attached file is a solution by PowerQuery.
See it.

 

[harivardhan]

Attached file is a solution by PowerQuery.
See it.

Hi @H.M , thanks for the file...but i have to use the power query on data that has 1000+ rows...and the method u gave is not suitable for dynamic data..

but still, thanks for the effort

 

[Debaser]

I don't know what the performance will be like with large data, but perhaps:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    H_List = List.Distinct(Source[Heirachy]),
    Starter = [hvals=List.Repeat({1}, List.Count(H_List) + 1), Qty = {}],
    CalcResult = List.Accumulate(Table.ToRecords(Source), Starter, (s, c)=>let newval = if c[Heirachy] = 1 then c[Quantity] else c[Quantity] * s[hvals]{c[Heirachy] - 2} in [hvals = List.ReplaceRange(s[hvals], c[Heirachy] - 1, 1, {newval}), Qty = s[Qty] & {newval}]),
    final = Table.FromColumns(Table.ToColumns(Source) & {CalcResult[Qty]}, Table.ColumnNames(Source) & {"Result"})
in
    final

 

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    rows = List.Buffer(
        Table.ToList(
            Source,
            (x) => {Text.From(x{0}), Text.From(x{0} - 1)} & x
        )
    ),
    gen = List.Generate(
        () => [i = 0, c = rows{0}, res = c{4}, rec = Record.FromList({res}, {c{0}})],
        (x) => x[i] < List.Count(rows),
        (x) =>
            [
                i = x[i] + 1,
                c = rows{i},
                res = c{4} * Record.FieldOrDefault(x[rec], c{1}, 1),
                rec = x[rec] & Record.FromList({res}, {c{0}})
            ],
        (x) => List.Skip(x[c], 2) & {x[res]}
    ),
    result = Table.FromList(gen, (x) => x, Table.ColumnNames(Source) & {"Result"})
in
    result