Forum Discussion

pontushaglund's avatar
pontushaglund
Helper I
7 years ago
Solved

Finding first, second, third occurence

Hi there.   I have this table of data: 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,...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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
DaFloDo's avatar
DaFloDo
Resolver I
7 years ago

hi pontushaglund,

 

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

 

 

 

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