Forum Discussion
Separate date
I tried but its is not working.
let
Source = Excel.Workbook(File.Contents("M:\001\PLN\Rajkumar - CAFM\Daily report\Jan19\15 Jan19\TCD.xlsx"), null, true),
Export_Sheet = Source{[Item="Export",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Export_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Task ID", type text}, {"Building", type text}, {"Task Created", type datetime}, {"Due By", type datetime}, {"Finish Date", type datetime}, {"Seq", Int64.Type}, {"Sub Class", type text}, {"Description", type text}, {"Categories", type text}, {"Status", type text}, {"Name", type text}, {"Asset Code", type any}, {"Asset Description", type text}, {"Level of Completion", type text}, {"Type", type text}, {"Reported Date", type datetime}, {"Frequency", type any}, {"Historic Status", type any}, {"Contract", type text}, {"Long Description", type text}, {"Notes", type text}, {"Description_1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Due By", "Due By - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Text After Delimiter", "Second&ThirdPart", Splitter.SplitTextByDelimiter("/", TCD.xlsx), {"SecondPart", "ThridPart"}),
#"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Date", each if [Column2] = "DD/MM/YYYY" then Text.Combine({Text.From([FirstPart]), Text.From([SecondPart]), Text.From([ThridPart])}, "/") else Text.Combine({Text.From([SecondPart]), Text.From([FirstPart]), Text.From([ThridPart])}, "/")),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Due By - Copy", type date}})
in
#"Changed Type1"
Hi Magizhsoft,
I think its not working because of the #"Added conditional column" step. The Each If is still refering to [Column2]. This needs to be changed to the column where the MM/DD/YYYY or DD/MM/YYYY value is in.