Transform text (YYYYMMDD) to Date when some cells are empty

[Anonymous]

Hi,

In the Power Query Editior I have a column, lets call it "column" with a date in Text-format

• When Date is filled it will show: YYYYMMDD
• When Date is not filled it will show: 0

I would like to add a column and transform the Text to a Date (DD-MM-YYYY), but to leave emtpy when "0"

I would use this formular when all cells are filled, but it will not work with the cells containing "0":

Date =

Date.FromText(
Text.Range([column], 0, 4) &
"-" &
Text.Range([column], 4, 2) &
"-" &
Text.Range([column], 6, 2) )

How would I handle this in DAX?

[Fowmy · Accepted solution]

@Anonymous 

Add an if condition to check. 

=if [Column] = "0" 
then 0 
else
Date.FromText(
Text.Range([Column], 0, 4) &
"-" &
Text.Range([Column], 4, 2) &
"-" &
Text.Range([Column], 6, 2) )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks,

I wanted to lease cells emtpy when "0", so I tweaked it a bit:

if [column] = "0" then ""
else
Date.FromText( Text.Range([column], 0, 4) &
"-" &
Text.Range([column], 4, 2) &
"-" &
Text.Range([column], 6, 2) )