Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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 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])
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |