Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

For each group find first negative value and replace subsequent years with zero

Hello, how can I get the first negative number for each Group, Category and Country and then make the new value for that year and years after value zero?   Group Category Country Year Value ...
CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

Aside from calculated column with DAX, Table.MinN() in PQ easily does the trick in your scenario. Since the large dataset is logically sliced into many small ones, I expect the performance is satisfactory.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJDsAgCAXQu7DGBBw6LHsOw/2vUaBNuuh3Yb74VBLmJCWmy1dkFT08hpDxT84MIH7IVBSSfo/qK+P5TvISAF1BlB1By/5Iem4BRJSGZPOAsC+7xMwqmd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Category = _t, Country = _t, Year = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Category", "Country"}, {{"ar", each let tb = Table.MinN(_, "Year", each [Value] > 0), tb0 = Table.ReplaceValue(Table.Skip(_, Table.RowCount(tb)), each [Value], 0, Replacer.ReplaceValue, {"Value"}) in tb & tb0}}),
    #"Combined Tables" = Table.Combine(#"Grouped Rows"[ar])
in
    #"Combined Tables"