Forum Discussion

shekhar_shres's avatar
shekhar_shres
Advocate II
5 years ago
Solved

Categorising a Column based on Row Count

Hi all,

I hope all of you are having a great time. I need a help with Power Query.

I have the following Column.

I want to add a Category Column that depends on row as below. For example, the First 3 rows Category 1, row 3 to 6 Category 2 and so on. A business rule is that the category should increase after every 3 rows. 

How can I achieve this in Power Query?

Thanks in Advance



 

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    Simply adding 1 would solve it

     

    Table.AddColumn(Source, "Category", each "Category " & Number.ToText(Number.RoundDown(List.PositionOf(Source[ID],[ID])/3)+1))

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlGK1YlWMgWTZmDSHExagElLMGloAKEMIZSRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type", "New", each "Category " & Text.From(Number.IntegerDivide([ID]-1, 3)))
in
    Custom1

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    Hi,

     

    Paste this into your next step and replace Source with previous one.

     

    Categorized = Table.AddColumn(Source, "Category", each "Category " & Number.ToText(Number.RoundDown(List.PositionOf(Source[ID],[ID])/3)))
    • shekhar_shres's avatar
      shekhar_shres
      Advocate II
      5 years ago

      It started from 0 instead of 1 when I used the code. 


      When I included Roundup, it came as follows:


      We were close enough



      Thankyou,



      • Jakinta's avatar
        Jakinta
        Solution Sage
        5 years ago

        Simply adding 1 would solve it

         

        Table.AddColumn(Source, "Category", each "Category " & Number.ToText(Number.RoundDown(List.PositionOf(Source[ID],[ID])/3)+1))