Sort by Index

[Anonymous]

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 

[mahoneypat]

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

[v-janeyg-msft]

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]

Hi  v-janeyg-msft,

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

[Anonymous]

@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])