Change data type using direct query

[ling0028]

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.

[v-haibl-msft]

@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

[ling0028]

@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).

Thanks again!

Julia

[v-haibl-msft]

@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 )

Best Regards,

Herbert

[Anonymous]

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

[ling0028]

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.

[Anonymous]

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.