Sort Columns using Live Connection

[Anonymous]

I am working with a database live connection and I am trying to sort the columns from 1-12. However, PowerBI is not recognizing the right data type and it is ordering as 1,11,12, 2, 3, 4 ......9 

Is there a way to fix this issue?

 

[Anonymous]

Hi @Anonymous,

Did you try changing the data type of that column from text to whole number? You can do so from either Modeling tab on data view or transform tab while editing the query.

-Lakshmi

[Anonymous]

Thanks for your quick response @Anonymous. I tried to change the data type, but I am not able to do it. Since it is a live connection from a database that I do not control, I am not able to make any changes. Is there another way to bypass this problem?

[Anonymous · Accepted solution]

I understand the issue no. With live connection I don't think there is much modeling available, so everything needs to be handled in the data source. 

Make sure the data type is correct in your data source. You may try cleaning the column too, make sure there are no text values or blanks in that column. 

Thanks,

Lakshmi

[Anonymous]

Thanks for your help. I do not have direct control of the dataset, is it possible to create a measure that replicates the column that I want to work with?  Kind of like "Column A = Column B". I wrote this in measures:

helper_Month_column = VALUES('Calendar'[FYMnth]) 

helper_Month_colunm = VALUES('Calendar'[FYMnth])

 But did not work.

[Anonymous]

Do you know if it is possible to create a new column that is a replica of the one of the database? Thanks! @Anonymous 

[Anonymous]

I don't think it is possible. All the model changes need to be done backend with live connection.

Refer to similar question about creating columns with live data connection-

https://community.powerbi.com/t5/Desktop/Live-Data-Source-add-columns/td-p/551288

With Live connectivity, Power BI is just like a front end visualization tool,  to change the model. If you don't have access to the database directly, try changing to direct query or import connectivity if possible, then you can handle the model changes. 

Thanks,

Lakshmi