Add a column to create subsets: index rows from 1 to 3

[PaulDBrown]

How can I add a column in Power Query which basically indexes rows from 1 to 3 (I need to split the data into 3 subsets). For example:

Index	Field 1	Field 2	Field 3	Subset
1	A	aa	25	1
2	B	bb	25	2
3	C	cc	49	3
4	D	dd	38	1
5	E	ee	37	2
6	F	ff	42	3
7	G	gg	23	1
8	H	hh	31	2
9	I	ii	23	3
10	J	jj	27	1
11	K	kk	33	2
12	L	l	42	3

Thanks for your help

[Vijay_A_Verma · Accepted solution]

Insert an Index column (default base is 0 which should not be changed).
Select the column - Transform menu - Standard - Modulo - Value 3
Select the column - Transform menu - Standard - Add - Value 1

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj3ApCeY9FKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 3), type number}}),
    #"Added to Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index", each _ + 1, type number}})
in
    #"Added to Column"

[PaulDBrown]

Super. Thank you very much @BA_Pete !

[BA_Pete · Accepted solution]

Hi @PaulDBrown ,

Add a regular index column starting from zero, then a new column with this:

Number.Mod([Index], 3) + 1

Using your actual example data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY65DsJADET/xXUKvBtIKIFwJfzBKkXui/+vmQmKleJZ1uzsk0MQlUguoKow3BFDpYyCOGxXUNdb7tbcY7uBpsGIzxh+zWNsGWhbRql5+PMOuo55Yp4Ttgfoe3qcedh4gmFg1ZuHxhcYR1bVPLzgDaZp6/89esCag3nmQ2Ii5d8CLAvL3kzKIz7gu7uo/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Field 1" = _t, #"Field 2" = _t, #"Field 3" = _t, Subset = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Field 1", type text}, {"Field 2", type text}, {"Field 3", Int64.Type}, {"Subset", Int64.Type}}),
    addIndex3 = Table.AddColumn(chgTypes, "Index3", each Number.Mod([Index] - 1, 3) + 1)
in
    addIndex3

Pete

[Vijay_A_Verma · Accepted solution]

Insert an Index column (default base is 0 which should not be changed).
Select the column - Transform menu - Standard - Modulo - Value 3
Select the column - Transform menu - Standard - Add - Value 1

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj3ApCeY9FKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 3), type number}}),
    #"Added to Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index", each _ + 1, type number}})
in
    #"Added to Column"

[PaulDBrown]

Thank you very much, @Vijay_A_Verma !