Forum Discussion
For each category find first negative value and then keep at 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 ...
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"
ronrsnfld
Super User
Super UserI 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"