Power Query

[linkn00]

I am encountering a situation where the date is both text and number. the date is format as dd/MM/yyyy.
the other date is format as MM/dd/yyyy.
We can see it in excel but power query just shows everything as a date or error. I want to have it convert everything to month/date/year.Please help. thank you
View attachment 112141

 

[alansidman]

If they are both number dates in Excel and not text, then reformat them in Excel to Numbers. True Dates are numbers, 1 up from 1/1/1900. Then import them into PQ.

 

[linkn00]

If they are both number dates in Excel and not text, then reformat them in Excel to Numbers. True Dates are numbers, 1 up from 1/1/1900. Then import them into PQ.

they are numbers and text. i don't want to do this in Excel because this is not a one-time thing. I will get a new CSV file every day. this is the reason why i choose power query

 

[alansidman]

Suggest you offer up some sample data using XL2BB so that we can run some Mcode on some data that reflect some actual data. Your attachment is not visible to us.

 

[alansidman]

There may be an easier means but this will work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Is Date", each try Value.Is(Number.From ([Date]), type number) otherwise false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Is Date] = false then [Date] else null),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Custom.2", "Custom.1", "Custom.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Added Custom2" = Table.AddColumn(#"Merged Columns", "New Date", each if[Merged]="//"then [Date] else [Merged]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"New Date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"New Date"})
in
    #"Removed Other Columns"

Book3
	A	B	C
1	Date		New Date
2	1/3/2024		1/3/2024
3	6/5/2024		6/5/2024
4	25/12/2024		12/25/2024
5	16/10/2024		10/16/2024
6	6/4/2024		6/4/2024
Sheet1

 

[linkn00]

Suggest you offer up some sample data using XL2BB so that we can run some Mcode on some data that reflect some actual data. Your attachment is not visible to us.

sorry didn't know you could not see the picture. this is what the data looks like. i dont have the actual data with me but this is what it looks like

from a quick look at it, it looks like this		when setting the column to number it looks like this
​ 23/01/2024		23/01/2024
1/23/2024​		45314​

 

[alansidman]

See my post nr 5. I am in the US, so my default is mm/dd/yyyy You can modify my code to reverse it for your default if necessary.

 

[linkn00]

thank you so much