Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 Registered | Existing column - Training Type | New Custom Column - Due Due |
1/1/2023 | Online | 1/2/2023 |
1/1/2023 | Facetoface | 1/3/2023 |
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:
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!