Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
Martine10
Frequent Visitor

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?

 

GroupCategoryCountryYearValueNew value
1A120185050
1A120192020
1A12020-100
1A12021200

 

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?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"

 

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1638511524875.png

this is dax code

wdx223_Daniel_1-1638511822908.png

 

ronrsnfld
Super User
Super User

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
Super User
Super User

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:

Martine10_1-1639387977737.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors