Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kirvis
Helper I
Helper I

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
edhans
Super User
Super User

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
Jimmy801
Community Champion
Community Champion

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
Super User
Super User

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!


edhans
Super User
Super User

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

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
Bohumil_Uhrin
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors