Forum Discussion
jirim
4 years agoFrequent Visitor
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...
- 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"
CNENFRNL
4 years agoCommunity Champion
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"
- Anonymous4 years agoNot applicable
This is cool, what UI steps equal to Table.Group() ?
- jirim4 years agoFrequent Visitor
Absolutely amazing. It works great. But I am not sure if my mental capacity is capable to understand how 🙂
Thanks a lot