cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ICRdatalover Regular Visitor
Regular Visitor

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

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?

 

3.PNG

 

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: 

2.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
ICRdatalover Regular Visitor
Regular Visitor

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

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,

1.PNG

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.

2.PNG

 

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.

3 REPLIES 3
Kogikoski Frequent Visitor
Frequent Visitor

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

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 Regular Visitor
Regular Visitor

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

Hi @Kogikoski

 

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 Regular Visitor
Regular Visitor

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

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,

1.PNG

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.

2.PNG

 

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 263 members 3,097 guests
Please welcome our newest community members: