Sort using the Sort by Column Button not Working

[Gazzer]

I am trying to sort a simple table of data using the Sort by Column button on the Modeling ribbon (and the one on the Modeling tab). I can never get it to work. I have followed the steps from the directions on https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/ but the order never changes.

The column I am trying to use is a simple numeric index column, created using the built-in function. It doesn't work on any field I have tried, whether included in the table of displayed data or not.

Any suggestions welcome. 

[v-sihou-msft]

@Gazzer

@MattAllington is correct. It must be 1 to 1 relationship, which means it can only be one value for each entry in to column you want to sort. Otherwise, it will throw error message like below:

Regards,

[Gazzer]

Thanks for the info. However, I do not get any error message, the sort order simply does not change. 

I am unclear as to why there would need to be a 1:1 relationship (or any relationship) when I am using a field within the table I am attempting to sort. 

[shill1000]

Are you simply trying to sort a table by one column or is there a reason you are trying to use the 'Sort by Column' function?

The first can be done simply by using the table header to sort the table.

The second is an option to sort a visual by a column on a reference table. As indicated, the reference table must have a 1:1 relationship with the data in the table you want to sort. This is used when you have a specific sort order that needs to be applied, inctead of a generic alphnumeric sequence.

What exactly are you trying to achieve?

[Gazzer]

I am trying to display the data in the natural order, rather than by one of the visible fields. Essentially, I want to sort the visible data by a field which exists in the same table but which I do not want to display.

Imagine my table has four fields. I want to display fields one, two and three in table form, but I want it to be sorted by the values in field four. Sorting by any of the three visible fields is unsuitable. In the transformation I have added an index column to provide the natural order value and it is this field I want to sort by, but not display.

According to the documentation, this should be possible:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/#sort-using-the-sor...

[shill1000]

OK, so you appear to want to use the function as intended. I know it seems a daft question, but have you checked the order of the column you want to sort by? If it is in the same order as the column you are trying to sort it almost certainly result in no change. Perhaps a sample of the 2 columns would help us identify the issue?

[Gazzer]

Unfortunately I cannot show the actual data as it contains personal information. I can confirm that the natural order of the data is not the same as the column I wish to sort by - adding the index field in the displayed table shows this.

I will see if I can mock up something to demonstrate the issue without including personal data.

[Gazzer]

I want to sort by the 'Index' column, but without including it in the display table, as per below. The data is currently sorting by the Description column.

[shill1000]

I've tested this with simple sample data and it works fine - suggests the issue may lay in your data?

I created a query from the Excel file on the right, in which I sorted by the ID column. I then applied the 'Sort by Column' using the index column successfully, in the visual.

[Gazzer]

I have tried duplicating your sample, creating an Excel file, but it still does not sort. The only way I can get something which looks like what you have is to include the Index column in the display table and then reduce its width to zero so that it cannot be seen.

I appreciate your persistence with this!

[shill1000]

Very strange! Only thing I can think of is to check the datatype. In the sample, if you replcated exactly what I did, what datatype shows for each fo the 4 columns? Should be, in order, Index = Whole Number, ID = Text, Name = Text, Age = Whole Number. If any are a different data type that could be your issue. As you appear to be taking in data with German as he language I wonder if it is confusing the type detection? May have to update each type manually after import?

[Gazzer]

Not sure where you got German from - it is all English and typed in by hand, not imported. The index column is data type of whole number (as can be seen from my screenshot).

I did not copy your data - in my sample file there is only two columns - the numeric index and the text description, which I did to make it as simple as possible (again, visible in the screenshot).

Not sure where to go with this now - thanks for trying to help.

[Vvelarde · Accepted solution]

@Gazzer

Hi, Using your sample:

Select Description from Field Panel after that go To Sort By Column and Choose Index.

And Voila!!!. 

Like this:

Victor

Lima - Peru

[Gazzer]

Thank you Victor (and others) - the issue was me misreading the instructions. I was selecting the Index field in the list instead of the Description field.

[shill1000]

Sorry I got two threads mixed up! 

Just for the sake of sanity, can you try it using the same data as my sample and see if you get the same results? I think you need to rule out th esource data as a cause.

[MattAllington]

The target column ( column you want to sort) must have the identical grainularity of the sort column, and there must be a 1 to 1 match between the columns. So if you have a month name column, there are 12 unique values. The sort column must also have 12 values.  If 1 = Jan then 1 must always = Jan. The 1:1 match must never chabge

[Gazzer]

Thanks for the reply - none of what you describe is mentioned in the documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/#sort-using-the-sor...

I will try what you have described, but at the moment it feels like it will be impractical to implement (I accept I could be wrong)