Forum Discussion

jirim's avatar
jirim
Frequent Visitor
4 years ago
Solved

Transform date range to period range

Hello exprerts   My datasource is a s following:   Date from Date to Share 01.01.2020 15.03.2020 70 16.03.2020 15.08.2021 50 01.12.2021 31.01.2022 50 01.02.2022 31.05.2022...
  • CNENFRNL's avatar
    4 years ago

    A showcase of powerful Table.Group(),

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7RDcAgCATQXfg25sCgdhbj/msUYmhpavi5y5OwFoGrjUBAhVgrWoQB2mUR99Q5mB7Ygh5g31mia7FOPgASnQON0F/QH4AUEhh5w/wDRgJ20GXPL4aJfQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date from" = _t, #"Date to" = _t, Share = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date from", type date}, {"Date to", type date}, {"Share", Int64.Type}}, "fr"),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index"),
        Grouped =
            let rows = Table.ToRecords(#"Added Index") in Table.Group(#"Added Index", Table.ColumnNames(#"Added Index"), {"grp", each let rs = Table.ToRecords(_) in [From=Date.ToText(rs{0}[Date from],"yyyyMM"), To=Date.ToText(List.Last(rs)[Date to],"yyyyMM"), Share=rs{0}[Share]]}, 0, (x,y) => Byte.From(let r = rows{y[Index]-1} in y[Date from] <> r[Date to] + #duration(1,0,0,0) or y[Share] <> r[Share])),
        #"New Table" = Table.FromRecords(Grouped[grp])
    in
        #"New Table"