cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Change data type using direct query

Hello,

 

We are replicating SAP tables through SLT.

Date columns in SAP are stored as varchar(8) so SLT converts it on HANA using the same data type.

 

I'm trying to keep the direct query function and I've tried a couple of options that don't work.

 

1. Change the data type using Modeling funciton on Power BI.

It "converts" the data, but whenever I try to use it on a visual it gives the following error: Couldn't load the data for this visual. Unable to convert a value to the data type requested for table X column Y.

 

2. Creating a new column using TO_DATE or CAST as date on the direct query.

Power BI recognizes it as a date, but when trying to use a date slicer or any other date function HANA returns an error:  invalid DATE, ,TIME or TIMESTAMP value.

 

As well, any function (add, substract,etc) done on the new columns will still try to query the source, which will ask for a DATE but will return a VARCHAR. Error: Inconsistent datatype.

 

3. Any modification to the Advanced editor will break the direct query.

 

Anyone has a solution for this?

 

Thank you.

 

7 REPLIES 7
Highlighted
Microsoft
Microsoft

Re: Change data type using direct query

@ling0028

 

In direct query mode, we are able to select the data type in the Modeling tab. But before doing that, we should make sure the column has valid values. For example, if you want to convert a column values to Date type, there shouldn’t exist any weird value.

 

Best Regards,

Herbert

Highlighted
Frequent Visitor

Re: Change data type using direct query

@v-haibl-msft

 

Hi Herbert,

 

Thank you for your response.

I've already tried using the date function on the Modeling tab.

This data is coming from our SAP production environment, but to double check, I imported my date columns to make sure there are no weird values, and there are none.

 

First, SAP has the date values stored as varchar. Power BI is unable to convert directly from varchar to date (img 1), so in the Query I have to use either to_date or cast as date in order to bring a new column to my report.  Once I bring my new column into the report, Power BI recognizes it as a Date automatically, and I'm able to filter my data using a normal slicer. (img 2) But when trying to filter my data using the Timeline visual or any filtering on this column, I get the following error (img 3).

 

error1.pngerror2.pngPBIHANAerror.png

 

 

Thanks again!

Julia

Highlighted
Microsoft
Microsoft

Re: Change data type using direct query

@ling0028

 

If your original DATE values have the format like MMDDYYYY (e.g. 07032016), please try to create a new column with following DAX formula. Then try to change the Data Type of this column to Date. Please refer to my following screenshot.

 

DATE = 
LEFT ( 'Change data type using direct query'[DATE_GLTRP], 2 ) & "/"
    & MID ( 'Change data type using direct query'[DATE_GLTRP], 3, 2 )
    & "/"
& RIGHT ( 'Change data type using direct query'[DATE_GLTRP], 4 )

Change data type using direct query_1.jpg

 

Best Regards,

Herbert

Highlighted
Advocate II
Advocate II

Re: Change data type using direct query

Have you guyz found any solution for this? I am stuck with the same error

Highlighted
Advocate II
Advocate II

Re: Change data type using direct query

 
Highlighted
Frequent Visitor

Re: Change data type using direct query

Hello,

 

Sorry I was running out of time to deliver my project and didn't try Herbert's last suggestion.

 

I ended up using the import data functionality.

 

Thanks.

Highlighted
Advocate II
Advocate II

Re: Change data type using direct query

Thanks for the reply. I tried Herbert's method but it didn't work. It seems that timeline is not working for direct connections. Only working for imported data.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors