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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
bajimmy1983
Advocate V
Advocate 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
Advocate V
Advocate 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
Advocate V
Advocate 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!:
Power BI Cookbook Third Edition (Color)

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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