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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bajimmy1983
Helper V
Helper V

How to Add a Custom Column Date like (01-01-16, 01-02-16, etc)

Hello again community.

 

I think this very simple, but I cannot reach this outcome using Power Query (Add Custom Column feature). Cal you help me?

 

In one table I have Columns:

DAY = 01;

MONTH = 01, 02, 03 and so on; 

YEAR = 2016

 

Outcome: A Custom column that joins all three columns above and shows in date format DD-MM-YY (01-01-2016, 01-02-2016, 01-03-16

 

I have tried M lamguage = [DAY] & "-" & [MONTH] & "-" & [YEAR]

 

Result I got = Error

 

Thanks again in advance people.

Jaderson Almeida
Business Coordinator
2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

I just tried this and it worked like a champ. I made sure that all of my columns were Text though so perhaps that is your issue?

 

[Day] & "-" & [Month] & "-" & [Year]

Here is the query I used:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0oEQRgaGZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t, Month = _t, Year = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each [Day] & "-" & [Month] & "-" & [Year])
in
#"Added Custom"

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...

View solution in original post

In addition to previous comment, you could use
= Text.From([DAY]) & "-" & Text.From([MONTH]) & "-" & Text.From([YEAR])
Maxim Zelensky
excel-inside.pro

View solution in original post

5 REPLIES 5
ShNBl84
Helper II
Helper II

@bajimmy1983 

I recently needed to create a custom column in Power Query that detected if an invoice was before July 6, 2020. This code works and looks a bit simpler than the previous answers.

= [InvoiceDate] < Date.FromText("7/6/2020")

 

In addition to previous comment, you could use
= Text.From([DAY]) & "-" & Text.From([MONTH]) & "-" & Text.From([YEAR])
Maxim Zelensky
excel-inside.pro

Hello Hohlick,

I liked your way. Thanks a lot.
Jaderson Almeida
Business Coordinator
Greg_Deckler
Super User
Super User

I just tried this and it worked like a champ. I made sure that all of my columns were Text though so perhaps that is your issue?

 

[Day] & "-" & [Month] & "-" & [Year]

Here is the query I used:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0oEQRgaGZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t, Month = _t, Year = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each [Day] & "-" & [Month] & "-" & [Year])
in
#"Added Custom"

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 to your solution. Yep. You got it! 🙂
Jaderson Almeida
Business Coordinator

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.