cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION
Super User III
Super User III

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:

edhans_0-1602629949882.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
Super User III
Super User III

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

Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Super User III
Super User III

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:

edhans_0-1602629949882.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Frequent Visitor

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

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Helper II
Helper II

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors