Forum Discussion

ling0028's avatar
ling0028
Frequent Visitor
9 years ago

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 functi...
Need Help
v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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's avatar
    ling0028
    Frequent Visitor
    9 years ago

    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's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

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