Forum Discussion
How to add a custom column with surrogate grouping
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.
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.
