How to add a custom column with surrogate grouping

[kirvis]

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

[Jimmy801]

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

[PhilipTreacy]

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

[edhans · Accepted solution]

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]

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

[edhans]

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!

[Bohumil_Uhrin]

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}})