Transform date range to period range

[jirim]

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	60
01.06.2022	30.06.2022	60
01.07.2022	31.08.2022	60
01.10.2022	31.12.9999	100

Ie. date validity ranges for "Share" that do not overlap but can have "holes"

I need to transorm the above to period (month) ranges. If there are more than one value for the month the last one is used. If the subsequent ranges have same "Share" they should be joined.

For data as above the expected result is following

Period from	Period to	Share
202001	202002	70
202003	202108	50
202112	202201	50
202202	202208	60
202210	999912	100

I have already spent several hours trying to figure out the M-code for the transformation but failed.

Can anyone help?

[CNENFRNL · Accepted solution]

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"

[jirim]

Absolutely amazing. It works great. But I am not sure if my mental capacity is capable to understand how 🙂

Thanks a lot

[Anonymous]

This is cool, what UI steps equal to Table.Group() ?