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...
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.

     

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