How do I sort the values in the table in numerical order in Power BI Report Builder?

[HamidBee]

How do I sort the values in this table in numerical order in Power BI Report Builder?. It seems to only let me sort in alphabetical order.

 

 

[freddvalenzuela]

Buen aporte, ami me funciono!

Gracias...

[d_gosbell · Accepted solution]

So there are two options in relation to sorting in Report Builder. The best option is to do the sorting in your query if possible. If your query is in SQL or DAX you can do this by adding an ORDER BY to the end of your query.

But you can also do the sorting in the report itself as per the screenshot you provided in your original post. The A-Z and Z-A options do not indicate that it is doing a alphabetical sort, they are just indicating the sort direction. If the field is a numeric field then it will be sorted numerically.

[HamidBee]

Thanks a lot for your help. I tried both methods, worked perfectly. I noticed that if I use the ORDER BY clause in SQL and I sort the column in table properties. The sorting in table properties overrides the SQL sorting. I have two numerical fields. ID1 and ID2. In the SQL command I ordered by ID1 and then in Table properties I sorted by ID2. I just thought that was interesting. I'm sure you know this but I just wanted to write this here in case it benefits anyone else. 

I've noticed that the Report Builder community here is small compared to BI Desktop but I'm still glad we have a community on here. If you are interested in answering some of my BI Report Builder questions from yesterday please feel free. Thanks for the help.

[croberts21]

In the table Viz, click once on the ID header. It will sort numerically ONLY if ID is a number. It will not sort numerically if ID is a string.

PRO TIP: Always make ID a number when designing the table in the actual database. If you must, convert ID to a number in the Transform stage. 

To have multiple sort columns in a table viz, hold the SHIFT key, click once on each column header in the order you want them sorted. The first column you click is the primary sort, the second column is the secondary sort, etc.

[HamidBee]

Are you referring to this?

 

[croberts21]

Yes, that ID. I can't tell if it's formatted to be a number or text.

We have a database where the ID is a number, and it's incremented, but it's still a text field. We didn't design the database, our vendor did, and I don't know why they did that.

I don't recognize Power BI in your screenshots. Which version of PBI do you have?

[HamidBee]

I check the column type in SSMS and it is 'float'. Would that be acceptable? Just curious, you mentioned I could change it to a number in the 'Transform stage'. How can I do that?

So do I just left click the gray area on top of the header and that's it?.

If the column is a text field am I correct in assuming that the above method wouldn't work and that one has to go to table properties-> sorting?.

I've only been using SSMS and Report Builder a short time so I'm still trying to figure out some of the things. 

[croberts21]

You don't have to change the field type to number since ID is already a float, which means a number with an optional decimal point. But here is how to do it anyway.

1. Open your PBIX file. 
2. On the Home tab bar click "Transform data". The Transform window will open up. 
3. On the far left of the Transform window click the table  namethat contains the field you want to change. 
4. Right click the field header, click Change Type, click the type you want like Whole Number.

Now click the Close and Apply button if you are done with the Transform step.

[HamidBee]

I believe there may be some confusion here. My question is regarding Power BI Report Builder not Power BI Desktop. 

The solution you have provided for sorting the tables does not work in Report Builder and there is no power query in Report Builder. 

[croberts21]

I have never heard of PBI Report Builder so that's where the problem lies. lol. Sorry! 🙂

[HamidBee]

It's used for building paginated reports. Perfect for financial statements for example. You can use parameters to filter the statements by employee/date etc. Share the files on BI Service. A very useful skill to have especially given that not too many people know it in comparison to BI Desktop. Someone please correct me if I'm wrong (since I'm new to it  myself) but you would need to know SQL and have atlease SSMS up and running on your machine to get started. You can't import files like we do in BI Desktop. It needs to be queried from a database.