Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Index column based on value/category

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 ...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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.

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

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.