cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
mooref
New Member

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

 

1 REPLY 1
adudani
Super User
Super User

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:

 

adudani_0-1678313665779.png

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors