Replace date column wich have rows with just "0" to empty/null

[ICRdatalover]

Hi everyone, 

I have a "silly" problem i think, but i couldnt find the solution to this issue trough the forum. 

The problem is that I have a Date column with some "empty" rows showing a "0", and it shows the tipical error "DataFormat.Error: We couldn't parse the input provided as a Date value."

I cant change it from the source so, how can I replace this "0" from PowerQuery without deleting any other "0" from the column?

I mean, if I have dates like 2019-08-12 and I use the "replace values" from PowerQuery to eliminate this "error" from "0" to nothing or blank, this function will replace all the zeros from the column, leaving my dates like this: 

I just want to eliminate the zeros in the rows that have just a zero not the whole date. Im pretty sure this issue is a simple one but Im struggling with it and couldnt find any similar yet. Totally stucked with this......

Any help would be really aprecciate it!!!!

Thank you in advance.

Kind Regards

ICR

[Anonymous]

Hi,

I tried to replicate it here and it worked fine for me.

Maybe you can try to change the M code directly and and change 0 to "0" to see if it solves the problem.

Try to convert the column to text before doing that.

Hope that works,

Vagner

[ICRdatalover]

Hi @Anonymous, 

Thanks for the fast reply, unfortunately, your idea doesnt work, I already tried to do it before, it was one of the first things I tried on PowerQuery.

Are you sure you replicate it and worked fine? It seems that its not a mistake or some easy function Im forgetting, looks like a something to fix with "M" code in the advance editor (I hope its possible).

Thank you.

[ICRdatalover · Accepted solution]

Hi everyone, 

I just figured out how to replace "0" from Date tables without replacing all "0".

The thing is that, in PowerQuery, if you use the replace option like (Replace "0" with "nothing") like this,

It replaces ALL the zeros from all the columns. 

But if instead of that, you replace it writing the word "null" like the next picture, the function works as it should, replacing just the rows with only a single zero.

After that, convert again the columns from text to dates and the all the nulls will not be "errors" anymore as PowerQuery treat them as "empty" rows.

Seems like the function doesnt work really well if you leave the second parameter empty, at least it is not really intuitive.

I will be more than happy to share an example of that if anyone have the same problem in the future. 

Kind regards. 

ICR.