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

[Anonymous]

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

[Bibiano_Geraldo · Accepted solution]

Hi @Anonymous ,
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:

[hnguy71]

Hi @Anonymous 

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: