CODEWITHSUNDEEP
type-conversionpowerbidax
MST QNB
MST QNB

Reputation: 293

Power BI convert eight digit yyyymmdd to date using DAX

I'm trying to convert eight digit yyyymmdd to date format with DAX function.

column = DATE(LEFT(TABLE[COLUMN],4),MID(TABLE[COLUMN],5,2),RIGHT(TABLE[COLUMN],2))

However, I've got an error because of the original column has some records with "00000000", so how can I make a default value with IF statement or are there any better solution?

Best regards

Upvotes: 5

Views: 23687

Answers (2)

Bryant Avey
Bryant Avey

Reputation: 78

What I typically do is just make 2 distinct Power Query steps and this is handled automatically.

  1. just make the yyyymmdd column a text column
  2. make the text column from step 1 a date column (when prompted, be sure to select 'Add New Step')
  3. replace errors with null

That's it. You can even Ctrl-Click to select multiple columns and combine them into the 1,2, and 3 steps with multiple columns.

Upvotes: 5

user3141505
user3141505

Reputation: 106

Please check out "ferror" function IFERROR(value, value_if_error) for more information please visit Microsoft MSDN with link below https://msdn.microsoft.com/en-us/library/ee634765.aspx

column = IFERROR( DATE(LEFT(TABLE[COLUMN],4),MID(TABLE[COLUMN],5,2),RIGHT(TABLE[COLUMN],2)), DATE(yyyy,mm,dd))

Upvotes: 3

Related Questions