How to Concatenate numbers as Dates DD/MM/YY

[bajimmy1983]

Hi folks, how are you?

So I have a simple doubt about "concatenate" values in Power Query. I mean simple to experts, LOL

Desire outcome: Custom column called FullDate showing 01/MM/YY e.g. 01/02/16 (I always want DAY as 01).

What I have in database:
Column called OriginalDate with DD/MM/YY (day can be from 01 up to 31).

So, as you already guessed I need a formula that will just replace whatever DAY I have from OriginalDate column to 01 and concatenate it with MONTH and YEAR from the same OriginalDate column in this format: 01/MM/YY.

Thanks a lot for your always great replies.

[bajimmy1983 · Accepted solution]

People, I think I could end up with a solution. I remember one of my last posts in this community, "How to Add a Custom Column Date like (01-01-16, 01-02-16, etc).

The solution was provided by @hohlick:

= Text.From([DAY]) & "-" & Text.From([MONTH]) & "-" & Text.From([YEAR])

So thinking about it, I have add four custom columns:

[Day] = 01; 

[Month] = Date.Month(OriginalDate); 

[Year] = Date.Year(OriginalDate); 

FullDate = Text.From([Day]) & "-" & Text.From([Month]) & "-" & Text.From([Year])

So, I think this is far from the best solution, but it returned the desire outcome I was looking for.

If there is another way to get the same result, please let me know! 🙂

[jessicammoss]

Hi Jaderson,

If your original column is of data type date, you can use:

NewDate = DATEADD(Table[OriginalDate], -1*DAY(Table[OriginalDate]) + 1, DAY)

If your original column is of data type text, you can use:

NewDate =  "01" & RIGHT(Table[OriginalDate_String], 6)

HTH,

Jessica

[bajimmy1983]

Obrigado @jessicammoss

[Greg_Deckler]

DateFormat = FORMAT([Date],"dd/mm/yy")

Assuming your date is coming in as a Date.

[bajimmy1983]

Hi smoupre,

Thanks for your reply, but maybe I could explain correct what I need.

I am already working inside Power Query (data uploaded from an Excel table) and in this imported table I have a column called OriginalDate (already formatted as Date type). Days I have from 01 up to 31.

Now I need to create a custom column and then concatenate days as always 01 + month + year from OriginalDate column.

e.g: 01/01/16 as 01/01/16, 02/01/16 as 01/01/16, 03/01/16 as 01/01/16 and so on.

[Sean]

@bajimmy1983 If you want to convert your column (follow the BLUE steps) if you want to create a copy of your column (the RED)

Let me know if you have any questions!