Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bajimmy1983
Helper V
Helper V

How to Concatenate numbers as Dates DD/MM/YY

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.
Jaderson Almeida
Business Coordinator
1 ACCEPTED SOLUTION
bajimmy1983
Helper V
Helper V

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! 🙂

 

Jaderson Almeida
Business Coordinator

View solution in original post

6 REPLIES 6
bajimmy1983
Helper V
Helper V

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! 🙂

 

Jaderson Almeida
Business Coordinator
jessicammoss
Advocate I
Advocate I

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

Obrigado @jessicammoss

 

Jaderson Almeida
Business Coordinator
Greg_Deckler
Super User
Super User

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

Assuming your date is coming in as a Date.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.
Jaderson Almeida
Business Coordinator
Sean
Community Champion
Community Champion

@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! Smiley Happy

 

2016-10-24 - Date Conversion.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.