Changing a data type in a custom column in direct query

[maurcoll]

Hi

I have a model that is using some tables that have been imported and some that are direct query. Due to the volume of data and how frequently i need it to refresh the tables that are direct query need to remain as direct query and the others to stay as import.

I have added a custom column to one of the direct query tables and i need to amend the type to whole number, when i do this i get a message to say: This step results in a query not supported in directquery mode switch all tables to import mode.

Is there a way i can change the data type

This is the custom column i have created

if [hour] = 7 then 0 else if [hour] = 8 then 1 else if [hour] = 9 then 2 else if [hour] = 10 then 3 else if [hour] = 11 then 4 else if [hour] = 12 then 5 else if [hour] = 13 then 6 else if [hour] = 14 then 7

and so on for the day

i need to show the hour sorted so it starts at 7 on a chart/matrix

[vojtechsima · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[vojtechsima · Accepted solution]

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

[maurcoll]

Thank you , i created a new dimension table and that has worked