Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
45 | |
28 | |
28 | |
20 | |
13 |
User | Count |
---|---|
68 | |
60 | |
42 | |
28 | |
22 |