For each category find first negative value and then keep at zero

[Martine10]

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	New value
1	A	1	2018	50	50
1	A	1	2019	20	20
1	A	1	2020	-10	0
1	A	1	2021	20	0

PS: It concerns a table with about 840.000 rows. (Groups (3) x categories (20) x countries (100) x years  (140). In terms of performance, it it best to do this in Power Query or in DAX?

[wdx223_Daniel]

there are 840000 rows, it is not the better choice to use Power Query

this is dax code

[ronrsnfld]

I suspect @AlexisOlson  method may be faster, but another method worth trying might be to do all the work within the Table.Group.

You can use List.Generate to loop through each grouped subtable, setting a flag (pv in the code) to let you know whether the new value (nv in the code) should be the actual value or set to zero

//Replace first lines with however you get your data into the table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Group", Int64.Type}, {"Category", type text}, {"Country", Int64.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
//add the New value column to each grouped subTable
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Category", "Country"}, {
        {"New Table", (t)=> Table.FromColumns(
            {t[Year]} & {t[Value]} &

//use list generate to set a flag when an entry is less than zero
//then check that flag when generating the new entries
            {List.Generate(
                ()=>[nv=if t[Value]{0} < 0 then 0 else t[Value]{0}, 
                    pv=if t[Value]{0}<0 then -1 else 0, idx=0],
                each [idx] < Table.RowCount(t),
                each [nv= if t[Value]{[idx]+1} <0 or [pv] < 0  then 0 else t[Value]{[idx]+1}, 
                        pv = if [pv]<0 or t[Value]{[idx]+1} <0 then -1 else 0, idx=[idx]+1],
                each [nv])},
                {"Year","Value","New Value"}
                )                
                }}
                ),

    #"Expanded New Table" = Table.ExpandTableColumn(#"Grouped Rows", "New Table", 
            {"Year", "Value", "New Value"}, 
            {"Year", "Value", "New Value"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded New Table",{
        {"Year", Int64.Type}, {"Value", Int64.Type}, {"New Value", Int64.Type}})
in
    #"Changed Type1"

[AlexisOlson · Accepted solution]

You can calculate the first negative year by filtering for negative values and grouping on the first three columns and taking the minimum over the Year column. Merge this minimal year calculation back into your original table and compute [New value] using a comparison between Year and FirstNegativeYear.

Sample query you can paste into the Advanced Editor in your query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgCEV34awJoCTtse0Yxv3XEJHUJuVAvuH5gNaAIMGlNZORDg1B6OlHTouAaDNBphDRllift1bZkkRgKj5sdh77916Ql1N9Td2kYEDWbX6B+CL+jCMOkFlVpT4A", 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,{{"Group", Int64.Type}, {"Category", type text}, {"Country", Int64.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] < 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Group", "Category", "Country"}, {{"FirstYearNegative", each List.Min([Year]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Group", "Category", "Country"}, #"Grouped Rows", {"Group", "Category", "Country"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"FirstYearNegative"}, {"FirstYearNegative"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "NewValue", each if [FirstYearNegative] <> null and [Year] >= [FirstYearNegative] then 0 else [Value], Int64.Type)
in
    #"Added Custom"

[Martine10]

Great, thank you very much! This works correctly in the Power Query editor. However when I apply changes and refresh the data, the data of the new columns is not showing up in the dashboard. The columns are visible but all the rows are blank:

Do you have any idea what can be causing this problem? I have tried the following:
- refreshing all data

- emptying cache

- closing and reopening power bi 

- tried it with a new query table (with no relationships to other tables)

[AlexisOlson]

Strange. I can't think of a good reason why it would work in the query editor but not when you load it.