Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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
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.
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.
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
Thank you , i created a new dimension table and that has worked
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |