Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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:

     

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
    hnguy71
    Super User
    1 year ago

    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: