Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sort by Index

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

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 

sort.PNG

4 REPLIES 4
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


v-janeyg-msft
Community Support
Community Support

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.

vjaneygmsft_0-1634894849232.png

vjaneygmsft_1-1634895118869.png

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
Not applicable

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

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.