Check next value greater than zero and fill previous row by sub 3600

[Singh_10]

Hi All,

I am facing issue in one scenario--

in value min and value max column i want ot check the next value greater than zero and then reduce 3600 each time to fill the previous row for example previous value is -3600 from the next non zero value this is reducing 1 hour(3600 sec)

value_min	new Value_Min
0	386271	-3600
0	389871	-3600
0	393471	-3600
0	397071	-3600
400671	400671

[dufoq3 · Accepted solution]

Hi @Singh_10, I expect that you have multiple itemid's so this query consider every id separately:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tY8xDgAhCAT/Ym3BuiD8xfj/b1wiVtpcY0GyYZqZMQqMdCm15M366KUi3bFGk8AJG5DQ+g3NN4wQvFP8GUK4LR2qXq5US1fGXcnYlYpVOT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [itemid = _t, value_min = _t, value_max = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"itemid", Int64.Type}, {"value_min", Int64.Type}, {"value_max", Int64.Type}}),
    fn_GenerateMinMax = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[itemid=153370]}[All],
            _Detail = myTable,
            _RemovedOtherColumns = Table.SelectColumns(_Detail,{"value_min", "value_max"}),
            _Buffered = Table.Buffer(_RemovedOtherColumns),
            _GeneratedMinMax = List.Generate(
                ()=> [ x = Table.RowCount(_Buffered)-1, minHelper = _Buffered{x}[value_min], min = minHelper, maxHelper = _Buffered{x}[value_max], max = maxHelper ],
                each [x] >= 0,
                each [ x = [x]-1,
                    minHelper = _Buffered{x}[value_min],
                    min = if minHelper <> 0 then minHelper else if [min] <> 0 then [min]-3600 else 0,
                    maxHelper = _Buffered{x}[value_max],
                    max = if maxHelper <> 0 then maxHelper else if [max] <> 0 then [max]-3600 else 0 ],
                each [value_min = [min], value_max = [max]] ),
            _ReversedToTable = Table.FromRecords(List.Reverse(_GeneratedMinMax), type table[value_min=Int64.Type, value_max=Int64.Type]),
            _DetailRemovedColumns = Table.RemoveColumns(_Detail, {"value_min", "value_max"}),
            _CombinedTables = Table.FromColumns(Table.ToColumns(_DetailRemovedColumns) & Table.ToColumns(_ReversedToTable), Value.Type(_DetailRemovedColumns & _ReversedToTable))
        ][_CombinedTables],
    GroupedRows = Table.Group(ChangedType, {"itemid"}, {{"All", each _, type table}, {"GeneratedMinMax", fn_GenerateMinMax, type table}}),
    CombinedGeneratedMinMax = Table.Combine(GroupedRows[GeneratedMinMax])
in
    CombinedGeneratedMinMax

[Singh_10]

@dufoq3  great stuff 🙂

[dufoq3]

Enjoy 😉