Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
maurcoll
Helper III
Helper III

Changing 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

2 ACCEPTED SOLUTIONS
vojtechsima
Super User
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:

vojtechsima_0-1730979361903.pngvojtechsima_1-1730979373671.png

and then it's gonna be automatically sorted:

vojtechsima_2-1730979393611.png


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

View solution in original post

v-linyulu-msft
Community Support
Community Support

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.

vlinyulumsft_0-1731043376580.png

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

vlinyulumsft_1-1731043376580.png

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:

vlinyulumsft_2-1731043408359.png

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.

vlinyulumsft_3-1731043408360.png

vlinyulumsft_4-1731043431614.png

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

vlinyulumsft_5-1731043431615.png


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.

View solution in original post

3 REPLIES 3
v-linyulu-msft
Community Support
Community Support

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.

vlinyulumsft_0-1731043376580.png

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

vlinyulumsft_1-1731043376580.png

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:

vlinyulumsft_2-1731043408359.png

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.

vlinyulumsft_3-1731043408360.png

vlinyulumsft_4-1731043431614.png

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

vlinyulumsft_5-1731043431615.png


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
Super User
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:

vojtechsima_0-1730979361903.pngvojtechsima_1-1730979373671.png

and then it's gonna be automatically sorted:

vojtechsima_2-1730979393611.png


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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.