power query group by consecutive numbers

[Anonymous]

Hi, I need to group by consecutive numbers (groups of 3), starting from the latest (in this example the first group would be 34,33,32), I need to named the group as the latest number.

This list is going to get bigger over the future months.

How can I achieve this in Power Query?

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JCcAwEEPRXuZs8CxeazHpv42EaEC+PnTQP0dcXeuqJkVayFNSJsQpA2KUDlFK+yU2JSCL4pBJMchIsV0tR/2iXLWLFBSkDXEK0sIoSAulIM2/288L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Index", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Index", Order.Descending}}),
    #"Add Index Group" = Table.Group(#"Sorted Rows","Index",{"IndexGroup",each _},0,(x,y)=>Number.From(Number.Mod(y,3)=1)),
    #"Expanded IndexGroup" = Table.ExpandTableColumn(#"Add Index Group", "IndexGroup", {"Month", "Index"}, {"Month", "Index.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded IndexGroup",{{"Index", Int64.Type}, {"Month", type date}, {"Index.1", Int64.Type}})
in
    #"Changed Type1"

Note: it is needed to sort the index column by Descending.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[parry2k]

@Anonymous so your example is a bit confusing, so the latest value is 43 and you want to take that as a base and group backward in groups of 3, is this what you are after?

[parry2k]

@Anonymous what is the logic to group 32/33/34 together? What is the pattern?

[Anonymous]

Hi, the logic is that I need to create dynamic quarters. I need to group those months by three , starting from the latest.