Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I'm looking to calculating a custom column in power query using a if statement.
I have this column 1 ( scac) that IF it equals to the specific codes below it would mutiple Column 2 (# of days) by $250.
Column 1 (scac) | Column 2 (# of days) |
MONO | 5 |
SHEK | 10 |
VTEC | 12 |
IF not then multiply column 2 (# of days) by $35.
I have this formula, below but it seems to not to be working,
If [SCAC] = "MONU" or [SCAC] = "SHEK" or [SCAC] = "VTEC" then [# "of Days in Detention"] * 250 else [# "of Days in Detention"] * 35
Solved! Go to Solution.
Hello @Shan_Drex12
the formula has to be like this
if [#"Column 1 (scac)"]="MONO" or [#"Column 1 (scac)"]="SHEK" or [#"Column 1 (scac)"]="VTEC" then [#"Column 2 (# of days)"]*250 else [#"Column 2 (# of days)"]*35
here the complete example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vX381fSUTJVitWJVgr2cPUGcgwNwLywEFdnEM9IKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1 (scac)" = _t, #"Column 2 (# of days)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1 (scac)", type text}, {"Column 2 (# of days)", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Column 1 (scac)"]="MONO" or [#"Column 1 (scac)"]="SHEK" or [#"Column 1 (scac)"]="VTEC" then [#"Column 2 (# of days)"]*250 else [#"Column 2 (# of days)"]*35)
in
#"Added Custom"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Shan_Drex12
the formula has to be like this
if [#"Column 1 (scac)"]="MONO" or [#"Column 1 (scac)"]="SHEK" or [#"Column 1 (scac)"]="VTEC" then [#"Column 2 (# of days)"]*250 else [#"Column 2 (# of days)"]*35
here the complete example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vX381fSUTJVitWJVgr2cPUGcgwNwLywEFdnEM9IKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1 (scac)" = _t, #"Column 2 (# of days)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1 (scac)", type text}, {"Column 2 (# of days)", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Column 1 (scac)"]="MONO" or [#"Column 1 (scac)"]="SHEK" or [#"Column 1 (scac)"]="VTEC" then [#"Column 2 (# of days)"]*250 else [#"Column 2 (# of days)"]*35)
in
#"Added Custom"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy