Formatting Dates using Power Query

Question

I am trying to create a single field with YYYYMMDD.

I also have a column with mm/dd/yy

I also have separate columns with YY MM and DD.

The problem is that I need to insert a leading 0 when the month and/or day is less than 10?

Any suggestions would be appreciated.

Thanks,

Fred Goldstein

Answer 1

Thanks to several of you for posting replies.

First you have confirmed for me that there's no click I have not seen with a hidden YYYYMMDD feature.

I am able to split the text.

However I am looking for a PQ instruction that can fill the MM or DD columns with a leading zero if the length of the MM or DD field is one digit.

Thanks,

Fred

Answer 2

Excel 365

With Power Query Text.PadStart()

https://www.mediafire.com/file/1wh9zov8oyz02kr/11_12_21.xlsx/file

https://www.mediafire.com/file/hvyfuvdusmmfev0/11_12_21.pdf/file

Answer 3

I am trying to create a single field with YYYYMMDD.

#1

I also have a column with mm/dd/yy

I also have separate columns with YY MM and DD.

The problem is that I need to insert a leading 0 when the month and/or day is less than 10?

Any suggestions would be appreciated.

Thanks,

Fred Goldstein

#1

Is that column in "date" format that Excel / PowerQuery recognize

Note that the date does have leading padding if Excel recognizes it as a date type.

My Windows Regional Setting for Short date includes DDD at the start. I did that so that the day displays in the taskbar clock ...

.

So, I'm guessing your "date" is actually in text format.

Use PowerQuery to convert text to date format.

.

Note that date in Excel is imported in DateTime format. I would convert it to Date only.

.

The display format of a date in excel is user controllable.

So, in this example the input and PQ output look the same by default

You can apply "custom formatting" to either column to get the desired look:

I changed the formatting to remove the spaces:

NOTE:

If your date is already in excel date format, you don't have to go through PowerQuery to apply the custom format

.

It would help if you explain some more what you are doing.

Where is this date coming from (ie CSV vs XLSX, or other)?

Where is it going? To be used in XLSX "report" or being output to some other system, ie via CSV or XLSX.

Answer 4

Hi Fred

Regarding, "... I also have a column with mm/dd/yy"

1. Format the above date column as Text
2. Then, split the column by "/" delimiter.

To finally "... have separate columns with YY MM and DD"

I hope this helps you

Regards

Jeovany

Answer 5

Hello LikedLeek60173, I warmly welcome you to the Community. I'm Israel and it is a pleasure to be able to support you today.

As far as i know, you can't do that because the field date types has a diferent format.

However, you can create a secundary column where you use the original date value and set the appropiate value as text, include the left digit.

Virtual Greetings and have a nice day!