Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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
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
Great! I never know that Date.From can add 1 as day value when the source date lacks day. Thanks for this simple solution!
Your code will work if you add a whitespace after 1. This can make them be recognized as date correctly.
Best Regards,
Jing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.