Index column based on value/category

[Anonymous]

Hi everyone, 

I want to create a Index column which is based on values in another column...

This is what my index column shows now:

Column 1	Index
Item A	1
Item A	2
Item B	3
Item B	4
Item C	5

But I want it to be like this:

Column 1	Index
Item A	1
Item A	1
Item B	2
Item B	2
Item B	2
Item B	2
Item C	3

Is there a way to achieve this?

Thank you very much!

Jasmin

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a new step with the following m codes.

= Table.AddColumn(#"Changed Type","Index",each 
let 
tab = Table.AddIndexColumn( Table.Group(#"Changed Type","Column 1",{"Data",each _}),"Temp",1,1,Int64.Type),
res = List.Sum( Table.SelectRows(tab,(x)=>x[Column 1]=[Column 1])[Temp])
in 
res
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @Anonymous 

you have to group-by columns you want to add the index to and apply the function of All rows to keep all information. Then add the index column, delete the group-by-columns and expand the AllRows.

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVVwVIrVwWQ6YWU6K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"AllRows", each _, type table [Column 1=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Column 1"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Column 1"}, {"Column 1"})
in
    #"Expanded AllRows"

transforms this

into this

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

[Anonymous]

Hi Thanks!!

I forgot to mention that I several other columns in this table. And I want the index column to refer just to one column *in this example column 1

Thanks!!

[edhans · Accepted solution]

@Anonymous - See this. You can do this 100% through the user interface. It works with any number of columns.

1. Group your table by the desired column you want the index based on. For the aggregation, type "All Rows" for the column name and then "All Rows" in the operation.
   1. 
2. Add the Index column.
3. CLick on the expansion button for the All Rows column and expand ALL columns except the one you based the index on.
   1. 
4. End result:
   1. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVVwVNJRMgZiE6VYHSQhEyC2QAg5AblmQGyKKgTSaGiAKmYOVoqhzBgh5Aw2CGxYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Test1 = _t, Test2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test1", Int64.Type}, {"Test2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"All Rows", each _, type table [Column 1=nullable text, Test1=nullable number, Test2=nullable number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Index", "All Rows", {"Test1", "Test2"}, {"Test1", "Test2"})
in
    #"Expanded All Rows"

  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.

[DanielaAmadeuPr]

Your instruction just saved my life!!!! Thank you.