Replace value in a field type date

[gpiero]

I need some advice to solve the following error.

I have imported a table from csv.

Some row contain the error showed above.  It is not an import error since the original document  does not contain any date in that field.

I expected NULL.

I have tried Replace Value in the Query Editor.

If I put "00.00.0000" as Value to Find the string is not reconigned.

Then I tried to change the type data from datetime to text but wihtouth success.

Could someone help me?

Thanks in advance.

[Greg_Deckler]

Any chance you could upload your CSV or a portion of it to OneDrive or something and share a link?

[gpiero]

Can I send the link in a PM?

[Greg_Deckler]

Sure

[gpiero]

done

[gpiero]

@Greg_Deckler

Can you confirm the link works?

Thanks

[Greg_Deckler]

The link worked. I did not have any errors in the "Data documento" column. I looked for that specific ID, 80000555 and the Data documento was 00.00.0000. Now, it imported as Text. And, I could not convert it to a Date column in DAX. So, I went back into Query Editor and changed it to Date. It basically error'd out all of the rows and I got blanks for "Data documento". My apologies, but apparently my English language version of Power BI does not understand your dates.

Here is what I would try, try leaving it as text, replace 00.00.0000 with "" (null) and then convert it to a Date in the query. I'm thinking that should work.

[gpiero]

 @Greg_Deckler

thanks for your support.

I tried to apply your method to replace 00.00.0000 with "" but it does not work in the pbix that I am using now.

Meanwhile I run a new export of the same data from ERP. I would like to examine the data with errors during the import, trying to edit them during that phase starting from scratch.

Sometimes it is useful

Now I know which fields and columns should be checked. And I'll try to apply the substitution mentioned by you.

Thanks

[v-shex-msft]

Hi @gpiero,

For your requirement, you can try to use replace error function to replace the error value to null or other default value.

Formula:

Table.ReplaceErrorValues(#"PreviousStep", {{"Date", null}})

Table.ReplaceErrorValues(#"PreviousStep",  {{"Date", Date.From("12.31.1899")}})

Sample:

Regards,

Xiaoxin Sheng

[gpiero · Accepted solution]

@v-shex-msft, @Greg_Deckler

thanks both of you for supporting me.

Well, as I told in my previous post I decided to start from scratch.

This time I selected Do not detect data type in Data Type Detection.

I was able to change in the column "Data Documento" the string "00.00.0000" into "".

Then I trasformed in the proper data type.

Now empty fields are showed as blank

Here the query in case you have some further advices.

let
    Source = Csv.Document(File.Contents("D:\Lavori\01.20_-_ POWER BI DATASOURCE\Delivery Dashboard\ZCSDLV.CSV"),[Delimiter="|", Columns=76, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Change Type"),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","00.00.0000","""""",Replacer.ReplaceText,{"Data documento"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each true),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows","00.00.0000","",Replacer.ReplaceText,{"Data consegna"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value1", each true),
    #"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows1","""""","",Replacer.ReplaceText,{"Data documento"}),
    #"Filtered Rows2" = Table.SelectRows(#"Replaced Value2", each true),
    #"Replaced Value3" = Table.ReplaceValue(#"Filtered Rows2","00.00.0000","",Replacer.ReplaceText,{"Data Mov.Merci Eff."}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"Data documento", type date}, {"Data consegna", type date}, {"Data Mov.Merci Eff.", type date}}),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each true),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"Peso totale", type number}, {"Peso netto", type number}, {"Volume", type number}, {"Val. netto", Currency.Type}}),
    #"Filtered Rows4" = Table.SelectRows(#"Changed Type1", each true),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Peso di Carico HU", type number}, {"PesoTotale HU", type number}, {"Lunghezza", type number}, {"Larghezza", type number}, {"Altezza", type number}}),
    #"Filtered Rows5" = Table.SelectRows(#"Changed Type2", each true)
in
    #"Filtered Rows5"

Thanks again for your kind support

gpiero

[Greg_Deckler]

Awesone! Glad you were able to get it sorted out!