Changing a data type in a custom column in direct query

Hello, maurcoll ,

given you have your SQL column that gives your the 0-7 value, you can either create another one, inverted 0--7 and sort your original column based on that.

Or create manual dimension like this:

and then it's gonna be automatically sorted:

don't forget to create relationship to your DirectQuery table and use dimension for the field in Visual

Thanks for the reply from vojtechsima , please allow me to provide another insight:

Hi, maurcoll 
 

You are correct. Based on my testing, attempting to convert the type to a whole number, which corresponds to Int64.Type in M language, is indeed unsupported in Power Query.

Therefore, we recommend converting your required custom column to a decimal number, which is represented as "number" in M language.

For further details, please refer to the documentation.
Int64.From - PowerQuery M | Microsoft Learn

DirectQuery in Power BI - Power BI | Microsoft Learn

In your case, both of these data types can meet your requirements. Here is a screenshot from the official documentation:

For more information, please consult the documentation.

Solved: Number vs Int64.type - Microsoft Fabric Community

Moreover, even though you cannot directly change the type to whole number in Power Query, you can select the column in the desktop version and modify it using the Column Tools.

Below are the final results, and I hope they meet your needs:

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.