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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pontushaglund
Helper I
Helper I

Finding first, second, third occurence

Hi there.

 

I have this table of data:

table.png

I need to calculate a penalty for each row where the penalty is increased for conscutive rows:

The first row with a value above threshold (Index 1, 5, 10) should get the value 1000.

The second consecutive row (Index 2, 6, 11) should get the value 2000.

The third and following rows (Index 3, 7-8, 12) shoud get the value 5000.

 

I could do a merge to find the preceding row for each row, see if above threshold is true or false and then know if it is the first case but would prefer a nicer way. Is there one?

 

Please note that the actual data set has data for every hour the last five years, in total about 44000 rows. My experience is that some DAX-examples that work fine on 20 rows never executes on 44000 rows. I would prefer a solution in Power Query.

 

Thanks,

 

Pontus

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

This could be another option for you:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),
    
    AddAboveThreshold = Table.AddColumn( ChangedType, "AboveThreshold", each [Value] > [Threshold], type logical),
    
    Grouping = Table.Group( AddAboveThreshold, {"AboveThreshold"}, {"AllRows", each _, type table}, GroupKind.Local),
    
    ValueMapping = #table( type table [Index3= Number.Type, AddedValue = Number.Type], { {1, 1000}, {2, 2000}, {3, 5000} } ),
    
    fnAddValue = ( tbl as table ) as table =>
        let
            AddIndxCol = Table.AddIndexColumn( tbl, "Index2", 1, 1 ),
            MergeTables = Table.NestedJoin( AddIndxCol, {"Index2"}, ValueMapping, {"Index3"}, "JoinedTable", JoinKind.LeftOuter ),
            ExpandCol = Table.ExpandTableColumn( MergeTables, "JoinedTable", {"AddedValue"} ),
            FillDown = Table.FillDown( ExpandCol, {"AddedValue"})
        in
            Table.SelectColumns(FillDown, {"Index", "Value", "Threshold", "AddedValue"} ),
    
    ModifyTableCell = Table.AddColumn( 
                                Grouping, 
                                "AddValue", 
                                each 
                                    if 
                                        [AboveThreshold] = true 
                                     then 
                                        fnAddValue( [AllRows] ) 
                                    else 
                                        Table.SelectColumns( [AllRows], {"Index", "Value", "Threshold"} ),
                                type table ),
                                
    ExpandedAddValue = Table.ExpandTableColumn(
                            ModifyTableCell, 
                            "AddValue", 
                            {"Index", "Value", "Threshold", "AddedValue"}
                      ),
    RemovedOtherColumns = Table.SelectColumns(
                                             ExpandedAddValue,
                                             {"Index", "Value", "Threshold", "AddedValue"}),
                                             
    ChangedType2 = Table.TransformColumnTypes(
                            RemovedOtherColumns,
                            {
                                {"Index", Int64.Type}, 
                                {"Value", type number}, 
                                {"Threshold", type number}, 
                                {"AddedValue", Int64.Type}
                            }
                        )
in
    ChangedType2

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

3 REPLIES 3
LivioLanzo
Solution Sage
Solution Sage

This could be another option for you:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),
    
    AddAboveThreshold = Table.AddColumn( ChangedType, "AboveThreshold", each [Value] > [Threshold], type logical),
    
    Grouping = Table.Group( AddAboveThreshold, {"AboveThreshold"}, {"AllRows", each _, type table}, GroupKind.Local),
    
    ValueMapping = #table( type table [Index3= Number.Type, AddedValue = Number.Type], { {1, 1000}, {2, 2000}, {3, 5000} } ),
    
    fnAddValue = ( tbl as table ) as table =>
        let
            AddIndxCol = Table.AddIndexColumn( tbl, "Index2", 1, 1 ),
            MergeTables = Table.NestedJoin( AddIndxCol, {"Index2"}, ValueMapping, {"Index3"}, "JoinedTable", JoinKind.LeftOuter ),
            ExpandCol = Table.ExpandTableColumn( MergeTables, "JoinedTable", {"AddedValue"} ),
            FillDown = Table.FillDown( ExpandCol, {"AddedValue"})
        in
            Table.SelectColumns(FillDown, {"Index", "Value", "Threshold", "AddedValue"} ),
    
    ModifyTableCell = Table.AddColumn( 
                                Grouping, 
                                "AddValue", 
                                each 
                                    if 
                                        [AboveThreshold] = true 
                                     then 
                                        fnAddValue( [AllRows] ) 
                                    else 
                                        Table.SelectColumns( [AllRows], {"Index", "Value", "Threshold"} ),
                                type table ),
                                
    ExpandedAddValue = Table.ExpandTableColumn(
                            ModifyTableCell, 
                            "AddValue", 
                            {"Index", "Value", "Threshold", "AddedValue"}
                      ),
    RemovedOtherColumns = Table.SelectColumns(
                                             ExpandedAddValue,
                                             {"Index", "Value", "Threshold", "AddedValue"}),
                                             
    ChangedType2 = Table.TransformColumnTypes(
                            RemovedOtherColumns,
                            {
                                {"Index", Int64.Type}, 
                                {"Value", type number}, 
                                {"Threshold", type number}, 
                                {"AddedValue", Int64.Type}
                            }
                        )
in
    ChangedType2

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Really impressive but I have to take a few hours to try to wrap my head around what it is you've done. I like it a lot!

DaFloDo
Resolver I
Resolver I

hi @pontushaglund,

 

I think, as you mentioned, merging to the preceeding two rows would be the best way.

 

2018-11-01 11_47_09-Unbenannt - Power Query-Editor.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Above Threshold", each if [Value] > [Threshold] then "TRUE" else "FALSE"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Above Threshold", type logical}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index.1", 0, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index.1", "Previous"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Previous"},#"Renamed Columns",{"Index"},"Renamed Columns",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Above Threshold"}, {"Renamed Columns.Above Threshold"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded {0}",{{"Renamed Columns.Above Threshold", "Prev.Above Threshold"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Benutzerdefiniert", each [Previous]-1),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom1",{{"Benutzerdefiniert", "PrevPrev"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns3",{"PrevPrev"},#"Renamed Columns3",{"Index"},"Added Custom1",JoinKind.LeftOuter),
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom1", {"Above Threshold"}, {"Added Custom1.Above Threshold"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded {0}1",{{"Added Custom1.Above Threshold", "prevPrev.Above Threshold"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns2",{{"Index", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "penalty", each if  [Above Threshold] then 
   if [Prev.Above Threshold] <> null and [Prev.Above Threshold] then
     if [prevPrev.Above Threshold] <> null and [prevPrev.Above Threshold] then
       5000
     else
       2000
   else
      1000
 else
0)
in
    #"Added Custom2"

 

 

 

regards

 

florian

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.