Categorising a Column based on Row Count

[shekhar_shres]

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

[CNENFRNL]

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]

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]

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 · Accepted solution]

Simply adding 1 would solve it

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