Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

power query group by consecutive numbers

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?

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

     

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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.