Power BI convert eight digit yyyymmdd to date using DAX

type-conversion powerbi dax
MST QNB picture MST QNB · Jan 2, 2018 · Viewed 15.3k times · Source

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

Answer

Bryant Avey picture Bryant Avey · Apr 24, 2018

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.

Related questions

How do I convert a String to an int in Java?

How can I convert a String to an int in Java? My String contains only numbers, and I want to return the number it represents. For example, given the string "1234" the result should be the number 1234.

Read More
How do I parse a string to a float or int?

In Python, how can I parse a numeric string like "545.2222" to its corresponding float value, 545.2222? Or parse the string "31" to an integer, 31? I just want to know how to parse a float str to a float, and (separately) an int …

Read More
Easiest way to convert int to string in C++

What is the easiest way to convert from int to equivalent string in C++. I am aware of two methods. Is there any easier way? (1) int a = 10; char *intStr = itoa(a); string str = string(intStr); (2) int a = 10; stringstream ss; ss &…

Read More