Forum Discussion

Lazurens's avatar
Lazurens
Frequent Visitor
3 years ago
Solved

Get the first value of multiple days

Hello everyone,    I have data in power bi, having    Case 1 :  Before :    NumFO TimeObs TimeReal  13-april 777154 90 100 14-april 777154 90 27 15-april 777154 90 0 ...
  • m_dekorte's avatar
    3 years ago

    Hi Lazurens 

     

    See if this works for you, just copy the full script into a new blank query.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNTDRNTJW0lEyNzc3NDUBMiwNgIShgYFSrA5QgQkOBUbmEHlTHPJA7bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, NumFO = _t, TimeObs = _t, #"TimeReal " = _t]),
        ChType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"NumFO", Int64.Type}, {"TimeObs", Int64.Type}, {"TimeReal ", Int64.Type}}),
        GroupRows = Table.Group(ChType, {"NumFO"}, {{"t", each 
            [
                a = Table.Sort( _,{{"Date", Order.Ascending}}),
                b = Table.ReplaceValue(a,each [TimeObs],each if a[Date]{0} = [Date] then [TimeObs] else 0,Replacer.ReplaceValue,{"TimeObs"})
            ][b] }}),
        Expand_t = Table.ExpandTableColumn(GroupRows, "t", {"Date", "TimeObs", "TimeReal "}, {"Date", "TimeObs", "TimeReal "})
    in
        Expand_t

     

    Grouped the data on NumFO, included a sort just in case and updated the value in the TimeObs column.

    with this result.

     

    I hope this is helpful