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 1Index
Item A1
Item A2
Item B3
Item B4
Item C5

 

But I want it to be like this:

 

Column 1Index
Item A1
Item A1
Item B2
Item B2
Item B2
Item B2
Item C3

 

Is there a way to achieve this?

 

Thank you very much!

 

Jasmin

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.

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        edhans
        Community Champion
        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.