Forum Discussion

mooref's avatar
mooref
New Member
3 years ago

Custom column to create due dates dependent on training type

Hi All - Hoping someone might be able to help me. I'm a new PowerBI Desktop user and have one dataset of training data that i'm trying to report on. I'd like to add a custom column that returns a deadline, dependent on the training type and the date the user was registered. Essentially if the user was registered on January 1 2023, I want my custom column to show a due date of 1 mth from date registered for 'Training Type' online and a due date of 2 mths for 'Training Type' Facetoface. Can anyone help with this? 

 

Existing column - Date RegisteredExisting column - Training TypeNew Custom Column - Due Due
1/1/2023Online1/2/2023
1/1/2023Facetoface1/3/2023

 

Need Help

1 Reply

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    hi mooref ,

     

    create a blank query. copy and paste the code below into the advanced editor:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq3ILC7JzEtXSM7PKc3NU9BVcEksSVUISk0HiqcWpaYo6WBRE1KUmJkHEgmpLEgFqvBLLVdwLi0uyc9VcIabU5oKwkqxOtFKhvqG+kYGRsZApf55OZl5ID1AEYgYmgK3xOTUkvw0IAlWZAxVFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Uppercased Text" = Table.TransformColumns(#"Promoted Headers",{{"Existing column - Training Type", Text.Upper, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Uppercased Text",{{"Existing column - Date Registered", type date}, {"Existing column - Training Type", type text}, {"New Custom Column - Due Due", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom ", each if [#"Existing column - Training Type"] ="FACETOFACE" then Date.AddMonths([#"Existing column - Date Registered"],2) else Date.AddMonths([#"Existing column - Date Registered"],1)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom ", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"New Custom Column - Due Due"})
in
    #"Removed Columns"

     

     

     

    the link to the solution : Dateadd.pbix

     

    Used a combination of Mastering IF Statements in Power Query - including Nested IF-Statements (gorilla.bi) and Date.AddMonths - PowerQuery M | Microsoft Learn

     

    This will convert the training type column into Upper case and check if the training type is equal to facetoface, if yes add 2 months else 1.

     

    Output: