Formatting Date column

[Anonymous]

Hi Folks, 

I have a date column (below snapshot) where the format post downloading the dump is incorrect. The format is dd-mm-yy but I need to change the date from 04-Dec-24 to 12-Apr-24 which is actually correct date (somehow day and month got interchanged post download). Also in the same column some date is in text format and need to convert it to dd-mm-yy format. 

Please let me know if above transformations can be done in power query

[ryan_mayu]

@Anonymous 

maybe you can create a custom column in PQ

=#date(Number.From(Text.AfterDelimiter([date],"/",1)),Number.From(Text.BeforeDelimiter([date],"/")),Number.From(Text.BetweenDelimiters([date],"/","/")))

[PhilipTreacy]

Hi @Anonymous 

If you are starting with dates like this

And you want to convert to dd/mm/yy so that the first row is 12th Apr 24, what are the 4th, 5th and 6th rows?

You can't mix formats in the same column so if you want to apply dd/mm/yy to rows 1 through 3, you must do the same with the rest of the rows.  But you can't because there isn't a month 28, 27 or 26.

So either your data is in mixed formats i.e. dd/mm/yy and mm/dd/yy or it's all in the same format which is dd/mm/yy.

If it's in mixed formats then you need to fix that in the source data.

So what you are asking can't be done with the data you are showing me.

Regards

Phil

Then choose Date Type : Date and Locale : English (United States)

[Anonymous]

  
@PhilipTreacy I guess the issue is at data source. The same column has mix of date formats as well as mix of data types. 
Going with your snapshot,

• First 3 rows are of data type: Date, but is mix of dd/mm/yy and mm/dd/yy. Also, these are historical data so instead of 12/04/24 (mm/dd/yy) it should have been 12/04/24 (dd/mm/yy) format. 
• Row 4,5,6 are of date type: text and format is mm/dd/yy. 
  
  So the only solution is the fix the data source right? But thanks and appreciate your time and efforts. Got to know the root cause atleast.

[PhilipTreacy]

Hi @Anonymous 

Not sure why you are saynig some rows are dates and some rows are text.  In this image all the rows are text - you can tell because the data type icon in the column header is ABC 

If you are certain that some rows are dd/mm/yy and others are mm/dd/y then the only way to fix that is by fixing the source data so the data is in one format only.

Phil

[PhilipTreacy]

@Anonymous 

The dates you are importing are in the format m/d/y but you are using a PC that uses d/m/y.

In this case you need to do what's called importing dates using locale - this blog post explains all

Change Type Using Locale with Power Query

regards

Phil

[Anonymous]

@PhilipTreacy  Thanks for yoru time and help but it still see the same date 04-Dec-2024 instead of 12-Apr-24. Also, the date in text format throws an error while doing above steps in power query.