Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
9 years ago
Solved

Replace value in a field type date

 

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.

Need Help
  • gpiero's avatar
    gpiero
    9 years ago

    Anonymous, 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

10 Replies