Convert Varchar data type field into Date and Integer in direct query

[Abale]

Hi Team, 

I started developing the report in PowerBI, I am using direct query and i got data everything in varchar data type. In my case i want to convert  contact_date(Varchar) into Contact_date(date) Type and Convert Sales_amount Varchar field into Sales_amount Integer data type.

I tried few other options but its difficult to get the appropriate output, Like i update the query with CAST and to_date but it result in native query failure and unsupported. 

is there any way to convert these fields into number and date??

Thank you in advance!!  

[Anonymous]

Hi @Abale ,

About the data type conversion, please refer to #limited-data-transformations.

In Direct Query mode, only some Power Query Editor features are supported, because data is not saved under Direct Query mode, and data is obtained directly from the data source, so query folding must be supported at each step.We can see that when we convert the character format to the number format, this step does not support query folding ("View Native Query" is grayed out)

If you want to modify the data type, you can try:

1.Modify the field type on the data source side.

2.Switch the model to Import mode.

3.Change the format of the column in the Modeling tab of Power BI Desktop.

4.To split and merge columns through the Power Query editor, please refer to 

Convert to date type in different formats in the "... - Microsoft Power BI Community

Decoding DirectQuery in Power BI Part 4: Complex C... - Microsoft Power BI Community

Best Regards,

Neeko Tang

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

[Abale]

Hi @Anonymous 

The solutions are not correct for my scenario. 

My scenario is i am using dierct query and i am trying to convert the varchar type into date and number but i am not able to do.

While i am tyring to do over the sql query it is showing native query error and while i am doing over the desktop i am getting below mentioned error, 

Can you please provide appropirate answer for the given scenairo. 

Thank you

[Anonymous · Accepted solution]

Hi @Abale ,

Please try to modify the field type on the data source side (sql database).

Best Regards,

Neeko Tang

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