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 dea...
Need Help
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: