Forum Discussion
Separate date
Hi,
I have more than 100 records with the format of MM/DD/YYYY and DD/MM/YYYY in one column , can anyone help me to make it one format.
01/01/19 ,MM/DD/YYYY
02/01/19, MM/DD/YYYY
13/01/19 ,DD/MM/YYYY
14/01/19 ,DD/MM/YYYY
3 Replies
Hi Magizhsoft, I would split the column in the query editor and put it back together in the desired order. Something like this:
let
Source = Excel.Workbook(File.Contents("YOURSOURCEHERE"), null, true), Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data], #"Inserted First Characters" = Table.AddColumn(Blad1_Sheet, "FirstPart", each Text.Start([Column1], 2), type text), #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted First Characters", "Second&ThirdPart", each Text.AfterDelimiter([Column1], "/"), type text), #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Text After Delimiter", "Second&ThirdPart", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"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(#"Added Conditional Column",{{"Date", type date}})
in
#"Changed Type1"Hope this helps!
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.
