March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to 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"
there are 840000 rows, it is not the better choice to use Power Query
this is dax code
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"
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"
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)
Strange. I can't think of a good reason why it would work in the query editor but not when you load it.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.