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 docume...
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

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

 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 :smileyhappy:

 

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

 

Thanks

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    gpiero
    Icon for Skilled Sharer rankSkilled Sharer
    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

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