Forum Discussion
Helper IVChanging a data type in a custom column in direct query
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
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 VisualThanks 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 LearnDirectQuery 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.
3 Replies
- vojtechsima
Super User
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
maurcollThank you , i created a new dimension table and that has worked
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 LearnDirectQuery 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.