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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jeongkim
Post Partisan
Post Partisan

create custom column using M code to make Date from source name text

Hi,

 

I hope to create custom column using M code to make Date from 'Source Name' text. 

 

e.g. if Source Name contains 'Q1', then create custom column as '03-31' which is in Date format MM-DD.

and for the rest as below:

Q2 > 06-30

Q3 > 09-30

Q4 > 12-31

 

 

jeongkim_0-1731773866134.png

 

1 ACCEPTED SOLUTION
Bibiano_Geraldo
Super User
Super User

Hi @jeongkim ,
To reach the desired result, follow the bellow steps:

1. In Power Query, right click on your last step and choose Insert Step After as shown bellow: 

Bibiano_Geraldo_0-1731775071022.png

 

2. In Formula bar, past the following M code as shown bellow:

= Table.AddColumn(#"PreviousStep", "Custom Date", each 
    if Text.Contains([Source Name], "Q1") then #date(Date.Year(DateTime.LocalNow()), 3, 31)
    else if Text.Contains([Source Name], "Q2") then #date(Date.Year(DateTime.LocalNow()), 6, 30)
    else if Text.Contains([Source Name], "Q3") then #date(Date.Year(DateTime.LocalNow()), 9, 30)
    else if Text.Contains([Source Name], "Q4") then #date(Date.Year(DateTime.LocalNow()), 12, 31)
    else null
)

 

Bibiano_Geraldo_1-1731775252195.png

 

Important: Make sure to replace "PreviousStep" with your previous step name and"Custom Date" with the name of column your want.

 

 

At this point, your table should look like this:

Bibiano_Geraldo_2-1731775296303.png

 

Make sure to change the column data type to Date:

Bibiano_Geraldo_3-1731775424414.png


Now your can close and apply:

Bibiano_Geraldo_4-1731775495570.png

 

After loaded data, select the created column in Power Query, make sure if the Data type is set to Date, and in format just type MM-DD, as shown bellow:

Bibiano_Geraldo_5-1731775747355.png

 

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue

View solution in original post

2 REPLIES 2
Bibiano_Geraldo
Super User
Super User

Hi @jeongkim ,
To reach the desired result, follow the bellow steps:

1. In Power Query, right click on your last step and choose Insert Step After as shown bellow: 

Bibiano_Geraldo_0-1731775071022.png

 

2. In Formula bar, past the following M code as shown bellow:

= Table.AddColumn(#"PreviousStep", "Custom Date", each 
    if Text.Contains([Source Name], "Q1") then #date(Date.Year(DateTime.LocalNow()), 3, 31)
    else if Text.Contains([Source Name], "Q2") then #date(Date.Year(DateTime.LocalNow()), 6, 30)
    else if Text.Contains([Source Name], "Q3") then #date(Date.Year(DateTime.LocalNow()), 9, 30)
    else if Text.Contains([Source Name], "Q4") then #date(Date.Year(DateTime.LocalNow()), 12, 31)
    else null
)

 

Bibiano_Geraldo_1-1731775252195.png

 

Important: Make sure to replace "PreviousStep" with your previous step name and"Custom Date" with the name of column your want.

 

 

At this point, your table should look like this:

Bibiano_Geraldo_2-1731775296303.png

 

Make sure to change the column data type to Date:

Bibiano_Geraldo_3-1731775424414.png


Now your can close and apply:

Bibiano_Geraldo_4-1731775495570.png

 

After loaded data, select the created column in Power Query, make sure if the Data type is set to Date, and in format just type MM-DD, as shown bellow:

Bibiano_Geraldo_5-1731775747355.png

 

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue
hnguy71
Super User
Super User

Hi @jeongkim 

You can achieve by using simple if conditions:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVMINFSK1YlWSjG2TMlWCDQCc4wNUrNT4gONwZyc7JSc7Kz4QBOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source Name" = _t]),

    // Custom column to derive date string
    AddDate = Table.AddColumn(Source, "Date", each 
    if Text.Contains([Source Name], "Q1", Comparer.OrdinalIgnoreCase) then "03-31" else 
    if Text.Contains([Source Name], "Q2", Comparer.OrdinalIgnoreCase) then "06-30" else 
    if Text.Contains([Source Name], "Q3", Comparer.OrdinalIgnoreCase) then "09-30" else 
    if Text.Contains([Source Name], "Q4", Comparer.OrdinalIgnoreCase) then "12-31" else 
    "")
in
    AddDate

 

Sample results:

hnguy71_0-1731775437426.png

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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