Forum Discussion
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
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!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!
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
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!
Perfect. Thank you!
Hello there,
I am having the same issue but your solution didn't work....
CAST(KPR_ptpi_amount as int) as (KPRAmount),
The error i get is:
DataSource.Error: Microsoft SQL: Incorrect syntax near '('.
Details:
DataSourceKind=SQL
DataSourcePath=rsobdcgenf;credopsreports
Message=Incorrect syntax near '('.
Number=102
Class=15What could i be doing wrong ?
There should be no parantheses (brackets) around your "(KPRAmount)" I think...
3 years late, but I'm here to save you!
