Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sort by Index

Hello,i have data with three Columns: "Index, Column Name and Value" and repeated data in Columns: Column Name and Value.

and need to display data in table with columns "Column Name and Value only" sort out "Column Name" by Index. Whenever i tried to sort out "Column Name" by "Index", it gives the error as below image.

Is there anyone who can help me to short out this issue 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous Index should be unique for each unique 'Column name'. As an option you can create additional index column with min index value for each value of 'column name':

    add_index_column=var current_name='table'[Column Name]

    return MINX(FILTER(ALL('Table'),'Table'[Column Name]=current_name),'Table'[Index])

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    To use 'sort by column' function, you must ensure that there are no duplicate values in the column.

    If you only put 'Column Name' and 'Value' column in table visual, It can only display unique combination values.

    Just like in your screenshot, only 2 rows will remain at the end. You must put the index column on it to display all the duplicate values. Then you can sort by index directly in visual.

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi  ,

       

      First of all thanks for your reply

       

      We can do sort by index column by adding index column

      But i don't want to display index column in table on report

      Could you please post better solution

       

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    You need to get an aggregate value of the index for each Vendor (e.g., Max or Min).  You can do it with DAX columns but you would need to create two to avoid a circular dependency.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  You can then use the new column as your sort by column.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYjOlWB0IzwiITeE8kJwJmOcElTOG84zBIshyhkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, Value = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Value", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "VSort", each let thisvendor = [Vendor] in List.Min(Table.SelectRows(Source, each [Vendor] = thisvendor)[Index]), Int64.Type)
in
    #"Added Custom"

     

    Also see this video - Let's Get It Sorted (in here) - PartOne - YouTube

     

    Pat