Forum Discussion

Wayne74's avatar
Wayne74
Helper I
2 years ago
Solved

Working out Days since based on a flag

The logic for this one has me stumped and I am hoping the super brain collective may be able to help me.  I have been using Power BI on and off for quite a while, but still have a long way to go and ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Wayne74, I'm not sure if this is what are you looking for:

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxU0lEyNtA3MNY3MjAyAXJCgkJdlWJ1oFJGlrilcOsytECScnP0CUaWM8cjh9tIU6y6jJKgukwxdIGlLHDKGCPJoJuHoSkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, Date = _t, #"Issue 1" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Issue 1", type logical}}),
    GroupedRows = Table.Group(ChangedType, {"RegNo"}, {{"Days since last FALSE ISSUE", each 
        [ a = List.Max(Table.SelectRows(_, (x)=> x[#"Issue 1"] = false)[Date]), //Max FALSE Date
          b = Duration.TotalDays(Date.From(DateTime.FixedLocalNow()) - a)
        ][b], Int64.Type}})
in
    GroupedRows
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Wayne74, I'm not sure if this is what are you looking for:

 

Before

 

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxU0lEyNtA3MNY3MjAyAXJCgkJdlWJ1oFJGlrilcOsytECScnP0CUaWM8cjh9tIU6y6jJKgukwxdIGlLHDKGCPJoJuHoSkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, Date = _t, #"Issue 1" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Issue 1", type logical}}),
    GroupedRows = Table.Group(ChangedType, {"RegNo"}, {{"Days since last FALSE ISSUE", each 
        [ a = List.Max(Table.SelectRows(_, (x)=> x[#"Issue 1"] = false)[Date]), //Max FALSE Date
          b = Duration.TotalDays(Date.From(DateTime.FixedLocalNow()) - a)
        ][b], Int64.Type}})
in
    GroupedRows