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

IBM CYYMMDD Date Conversion - Please tell me there's a better way?!

Hello all!

 

My office uses an IBM AS400 and I'm connecting to my data via ODBC connection.

 

I'm building a data model using multiple files each with multiple date fields, and the data coming from the IBM is always in CYYMMDD format and comes over recognized as a number (not text) and null values come over as zeros.

 

So I've got the following manual manipulation but I'm wondering if theres a faster way.  This is painful!  Here's what I came up with.  I have to do this manual process with every single stinkin' field!

 

  1. Change Data Type to text
  2. Replace all 0 values with null
  3. Create new custom column with formula:
    1. =Text.Range([*Your Date Field*],3,2) & "/" & Text.Range([*Your Date Field*],5,2) & "/" & Text.Range([*Your Date Field*],1,2)
  4. Change data type in new custom column to date type

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: IBM CYYMMDD Date Conversion - Please tell me there's a better way?!

@mesmollar

 

As your coming date is always CYYMMDD format, I don't think there's a better way than truncating the "C" from the data string via either Power Query or DAX. You may consider storing the data from IBM into relational database, then you can custom a function and use a stored procedure to execute the conversion.

 

Regards,

1 REPLY 1
Moderator v-sihou-msft
Moderator

Re: IBM CYYMMDD Date Conversion - Please tell me there's a better way?!

@mesmollar

 

As your coming date is always CYYMMDD format, I don't think there's a better way than truncating the "C" from the data string via either Power Query or DAX. You may consider storing the data from IBM into relational database, then you can custom a function and use a stored procedure to execute the conversion.

 

Regards,