cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcarnovale
Frequent Visitor

If staments

Hi

I am trying to create a custom column that calculates the unit savings if plant A were to use plant B pricing. How would I write an if statement that would only return the savings and return a value of 0 if it would cost more money. I want it to look something like the table below.

Plant APlant BSavings
10.500
3.751.901.85
0.701.150
21.250.75
1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Add this custom column in Power Query @jcarnovale 

if [Plant A] > [Plant B] then [Plant A] - [Plant B] else 0

It will return this data for you:

edhans_0-1612311866115.png

Then simply change the data type to a number. Here is the full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLQMzVQitWJVjLWMzcF8g31LCF8Az1zAzDf0BTMNwJzjICcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Plant A" = _t, #"Plant B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant A", type number}, {"Plant B", type number}}),
    #"Added Savings" = Table.AddColumn(#"Changed Type", "Savings", each if [Plant A] > [Plant B] then [Plant A] - [Plant B] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Savings",{{"Savings", type number}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User III
Super User III

Add this custom column in Power Query @jcarnovale 

if [Plant A] > [Plant B] then [Plant A] - [Plant B] else 0

It will return this data for you:

edhans_0-1612311866115.png

Then simply change the data type to a number. Here is the full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLQMzVQitWJVjLWMzcF8g31LCF8Az1zAzDf0BTMNwJzjICcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Plant A" = _t, #"Plant B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant A", type number}, {"Plant B", type number}}),
    #"Added Savings" = Table.AddColumn(#"Changed Type", "Savings", each if [Plant A] > [Plant B] then [Plant A] - [Plant B] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Savings",{{"Savings", type number}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

smpa01
Resident Rockstar
Resident Rockstar

@jcarnovale 

 

column = if ([plant a]>[plant b], [plant a]-[plant b],0)

 


New Animated Dashboard: Sales Calendar


jcarnovale_0-1612308539467.png

I get an error when this try this is there a differnent method.

smpa01
Resident Rockstar
Resident Rockstar

@jcarnovalelooks like you are trying this pqwry while I gave you the dax soluion.

in pqwry do the following

if [plant a]>[plant b] then [plant a] else [plant a]-[plant b]

New Animated Dashboard: Sales Calendar


parry2k
Super User III
Super User III

@jcarnovale try following columns

 

New Col = 
MAX ( Table[Plant A] - Table[Plant B], 0 )

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors