Forum Discussion
Index column based on value/category
Anonymous - See this. You can do this 100% through the user interface. It works with any number of columns.
- 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.
-
- Add the Index column.
- CLick on the expansion button for the All Rows column and expand ALL columns except the one you based the index on.
-
- End result:
-
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.- 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.
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
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!!
Anonymous - See this. You can do this 100% through the user interface. It works with any number of columns.
- 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.
-
- Add the Index column.
- CLick on the expansion button for the All Rows column and expand ALL columns except the one you based the index on.
-
- End result:
-
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.Your instruction just saved my life!!!! Thank you.
- 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.
