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

[mesmollar]

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

[emarc1]

Since I found this via Google, I thought I'd give my thoughts, even though I'm 4 years too late... Perhaps someone else will find it useful.

We also use an IBM AS400 / iSeries. I've tried a few methods previously but had settled on the following for a while:

#"Added Custom - Date" = Table.AddColumn(Source, "Date", each if [DateCol] = 0 then null else
Date.FromText(Text.From([DateCol] + 19000000)), type date)

This has served me well but I was trying to tidy up the code a bit because I use this in every report I make. First I wanted something that transformed the column rather than creating a new one... But in the end I actually found a method to do this that could  transform a whole list of columns with just two lines of code:

DateFunction = each if _ = 0 then null else Date.FromText(Text.From(_ + 19000000)),
DateConversion = Table.TransformColumns(Source,{{"DateCol_1", DateFunction, type date},{"DateCol_2", DateFunction, type date},{"DateCol_3", DateFunction, type date}})

I doubt this is actually much different from a performance angle but it does make it a lot tidier from a code perspective which is handy when you have many date columns! Potentially could be some issues around 1900-01-02 to 1900-02-29, based on another post I saw, but that shouldn't be an issue on AS400 data. More code could be added to handle 9999999 dates but that should be fairly simple change that could be made in the DateFunction section:

DateFunction = each if _ = 0 then null else if _ = 9999999 then Date.From("9999/12/31") else Date.FromText(Text.From(_ + 19000000))

Hopefully that helps other poor souls who have to work with AS400 data!

[mgonza1]

Hi! where can i run the two lines of code that you say?
Do i have to invoke a custom function?

Saludos!
 

[emarc1]

I'd just used it in Power Query. You can right click on an existing query in Power Query and open it with the advanced editor. The code I gave needs to go in the let block. I've put it straight after the 'Source' transformation function, but it could go elsewhere if adjusted. You'll need to adjust the array (the {{x},{y},{z}} bit) in the 'DateConversion' function for your dataset and also adjust any other functions after, that reference 'Source' to pull from 'DateConversion' instead. eg:

let
    Source = Odbc.Query("dsn=dsn_name", "select x from y when z"),
    DateFunction = each if _ = 0 then null else Date.FromText(Text.From(_ + 19000000)),
    DateConversion = Table.TransformColumns(Source,{{"DateCol_1", DateFunction, type date},{"DateCol_2", DateFunction, type date},{"DateCol_3", DateFunction, type date}}),
    #"Renamed Columns" = Table.RenameColumns(DateConversion,{{"DateCol_1", "Date 1 Column Name"}, {"DateCol_2", "Date 2 Column Name"}, {"DateCol_3", "Date 3 Column Name"}})

in
    #"Renamed Columns"

[v-sihou-msft · Accepted solution]

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