Forum Discussion

Magdoulin1's avatar
Magdoulin1
New Member
2 years ago
Solved

Replacing Errors in Power Query

Hello everyone,

 

I am trying to replace errors in one of the columns by the below function but it does not work, what kind of  modifction I need to apply in the below code?

 

That is a sample of my table: Table1

 

And below is the code:

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Service Period", type text}}),
#"Inserted Minimum" = Table.AddColumn(#"Changed Type", "Minimum", each List.Min({#date(Value.FromText(Text.End(Text.BeforeDelimiter([Service Period], ", "&"#(lf)"),4)),Date.Month(Date.FromText("1"&Text.BeforeDelimiter([Service Period], ", "&"#(lf)"))),1), #date(Value.FromText(Text.End(Text.AfterDelimiter([Service Period], ", "&"#(lf)"),4)),Date.Month(Date.FromText("1"&Text.AfterDelimiter([Service Period], ", "&"#(lf)"))),1)}), Int64.Type),
#"Inserted Parsed Date" = Table.AddColumn(#"Inserted Minimum", "Index", each Date.From(DateTimeZone.From([Service Period])), type date),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Inserted Parsed Date", {{"Index", List.Min({#date(Value.FromText(Text.End(Text.BeforeDelimiter([Service Period], ", "&"#(lf)"),4)),Date.Month(Date.FromText("1"&Text.BeforeDelimiter([Service Period], ", "&"#(lf)"))),1), #date(Value.FromText(Text.End(Text.AfterDelimiter([Service Period], ", "&"#(lf)"),4)),Date.Month(Date.FromText("1"&Text.AfterDelimiter([Service Period], ", "&"#(lf)"))),1)})}})
in
#"Replaced Errors"

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi Magdoulin1,

     

    Your requirement is unclear, what are you aiming to achieve?
    For example this will get what you appear to be after but other than that ???

     

    let
    Source = Table.FromList( {"May 2023,#(lf)June 2023"}, Splitter.SplitByNothing(), type table[Service Period=text] ),
    insertMinimum = Table.AddColumn(Source, "Minimum", each List.Min( List.Transform( Text.Split([Service Period], "#(lf)"), Date.From )))
in
    insertMinimum

     

    It yields this outcome.

    I hope this is helpful

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    Hi Magdoulin1,

     

    Your requirement is unclear, what are you aiming to achieve?
    For example this will get what you appear to be after but other than that ???

     

    let
    Source = Table.FromList( {"May 2023,#(lf)June 2023"}, Splitter.SplitByNothing(), type table[Service Period=text] ),
    insertMinimum = Table.AddColumn(Source, "Minimum", each List.Min( List.Transform( Text.Split([Service Period], "#(lf)"), Date.From )))
in
    insertMinimum

     

    It yields this outcome.

    I hope this is helpful

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      m_dekorte 

      Great! I never know that Date.From can add 1 as day value when the source date lacks day. Thanks for this simple solution!

       

      Magdoulin1 

      Your code will work if you add a whitespace after 1. This can make them be recognized as date correctly. 

       

      Best Regards,
      Jing