Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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:
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
)
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:
Make sure to change the column data type to Date:
Now your can close and apply:
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:
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:
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
)
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:
Make sure to change the column data type to Date:
Now your can close and apply:
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:
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: