Forum Discussion

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

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

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

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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"

     

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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