Reply
Member
Posts: 46
Registered: ‎10-03-2017
Accepted Solution

Convert Column from Text to Number in Direct Query

Hi Experts! 

I am running into an issue around trying to convert a column that is connected via direct query from a database from "Text" to "Number" type. I know that the proper way to do this is to make the adjustment in the database itself however I was wondering if there was an alternate way to do this in the query editor without having to switch everything to an Import? 

 

Thanks

 

Dan 


Accepted Solutions
Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Convert Column from Text to Number in Direct Query

Hi @danb,

 

What if while connecting to a database using DirectQuery you will use NativeQuery, where you can do whatever you want?

See example with CAST(CONVERT) below:

 

DQ cast.PNGDQ cast

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

View solution in original post


All Replies
Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Convert Column from Text to Number in Direct Query

Hi @danb,

 

What if while connecting to a database using DirectQuery you will use NativeQuery, where you can do whatever you want?

See example with CAST(CONVERT) below:

 

DQ cast.PNGDQ cast

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Community Support Team
Posts: 2,967
Registered: ‎02-06-2018

Re: Convert Column from Text to Number in Direct Query

Hi @danb,

 

By my tests, the solution from zoloturu could solve your problem.

 

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 46
Registered: ‎10-03-2017

Re: Convert Column from Text to Number in Direct Query

@zoloturu

Thank you for your reply. So with the Cast/Convert, does that make changes to the actual database or just modify the the data as it is queried into Power BI?

 

Dan 

Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Convert Column from Text to Number in Direct Query

@danb,

 

it acts as a SQL batch (syntax is unlimited). So if you enter a query as I mentioned then it will be send to the database (calculations done on db side) and a result will be returned to Power BI. Use just SELECT statement and you will be safe. 

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Member
Posts: 46
Registered: ‎10-03-2017

Re: Convert Column from Text to Number in Direct Query

Perfect. Thank you!