Forum Discussion

kirvis's avatar
kirvis
Helper I
5 years ago
Solved

How to add a custom column with surrogate grouping

Hi all,

 

I would like to add a (very simple) column to a table to create surrogate groups that I can then use to pivot the data. The current table does not contain any data that I can use for this, so Power Query does not understand how to pivot.

 

Basically, I need to add the following column to my table:

 

Group

1

1

1

2

2

2

3

3

3

4

4

4

etc.

 

Is there a way to do that with an M function?

 

Thanks!

 

Bas

 

 

Need Help
  • edhans's avatar
    edhans
    5 years ago

    Hi kirvis - Take a look at this code:

    let
    Source = {1..100},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1/3, Int64.Type),
    #"Inserted Round Down" = Table.AddColumn(#"Added Index", "Round Down", each Number.RoundDown(Number.Round([Index],8)), Int64.Type)
in
    #"Inserted Round Down"

    What I did is:

    • Create a fake column of 1 to 100 just to have some data.
    • Added an index. Then I changed the index to start at 1 and increment by 1/3.
    • Then I added a column with this formula: Number.RoundDown(Number.Round([Index],8))

    It returns this:

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

6 Replies

  • Bohumil_Uhrin's avatar
    Bohumil_Uhrin
    Helper II
    5 years ago

    Hi,

    firstly, add normal index column, name it "Index", starting from 0

    then add this step: 

    = Table.TransformColumns(previous_table, {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}})

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Hi kirvis - Take a look at this code:

    let
    Source = {1..100},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1/3, Int64.Type),
    #"Inserted Round Down" = Table.AddColumn(#"Added Index", "Round Down", each Number.RoundDown(Number.Round([Index],8)), Int64.Type)
in
    #"Inserted Round Down"

    What I did is:

    • Create a fake column of 1 to 100 just to have some data.
    • Added an index. Then I changed the index to start at 1 and increment by 1/3.
    • Then I added a column with this formula: Number.RoundDown(Number.Round([Index],8))

    It returns this:

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

    • kirvis's avatar
      kirvis
      Helper I
      5 years ago

      Thanks all of you for your answers, some of them are very elegant! Great!

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        Hey kirvis - glad you have a solution. That is the great thing about this community - you'll often get a number of different creative solutions!

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi kirvis 

     

    If you want the numbers in the column to start from 1 just use Bohumil_Uhrin solution and add 1 after the Number.IntegerDivide

     

    = Table.TransformColumns(previous_table, {{"Index", each Number.IntegerDivide(_, 3) + 1, Int64.Type}})

     

    Phil

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello kirvis 

     

    here another approach if you are interested in

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVOK1YlWKk5MAdNwHoSBKV+cCKbR5ROLcQgjG0u6bUQZi0e/UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Your Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Your Column", type text}}),
    SplitTable = Table.FromList(Table.Split(#"Changed Type",3), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddIndex = Table.AddIndexColumn(SplitTable, "Group", 1, 1),
    ExpandTable = Table.ExpandTableColumn(AddIndex, "Column1", {"Your Column"}, {"Your Column"})
in
    ExpandTable

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy