IF function with multiple criteria

[mdrammeh]

I need help creating a calculated field column in Power Query using the following criteria:

=IF([@[DESIGN DOLLARS]]=MAX(EY14:EY15180),"BEST PERFORMING YEAR",IF([@[DESIGN DOLLARS]]=MIN(EY14:EY15180),"LEAST PERFORMING YEAR",""))

I need a way to convert the following formula into Power Query language so that I could add a column to show these performances.

For a simlar view, to capture the following measures:

If the Cell value is in between 1-5 then "1-5 days".

If the Cell value is in between 6-10 then "6-10 days".

If the Cell value is in between 11-20 then "11-20 days".

If the Cell value is in between 21-30 then "21-30 days".

If the Cell value is greater than 30 than "greater than 30"

You assistance is highly appreciate.

Than you!

[GilbertQ]

You could complete it by clicking on Add Column in the ribbon, then "Add Custom Column"

Then you can put in the following syntax:

NOTE: When using the Power Query Language it is case sensitive.

if [Cell Value] >= 1 and [Cell Value] <= 5 then "1-5 Days"

else if [Cell Value] >= 6 and [Cell Value] <= 10 then "6-10 Days"

else if [Cell Value] >= 11 and [Cell Value] <= 20 then "11-20 Days"

else if [Cell Value] >= 21 and [Cell Value] <= 30 then "21-30 Days"

else "greater than 30"

That should be all you need on your new Custom Column

[Greg_Deckler]

Sorry, which formula are you trying to create in Power Query? Is it:

=IF([@[DESIGN DOLLARS]]=MAX(EY14:EY15180),"BEST PERFORMING YEAR",IF([@[DESIGN DOLLARS]]=MIN(EY14:EY15180),"LEAST PERFORMING YEAR",""))

or:

If the Cell value is in between 1-5 then "1-5 days".

If the Cell value is in between 6-10 then "6-10 days".

If the Cell value is in between 11-20 then "11-20 days".

If the Cell value is in between 21-30 then "21-30 days".

If the Cell value is greater than 30 than "greater than 30"

And does it have to be done in Power Query or could it be done in the data model in DAX? And does it need to be column or could it be a measure. Finally, some sample data and expected results would greatly help!

[mdrammeh]

They are two separate examples. The first example would be something like this where am trying to capture the best performing year in Power Query. I want the formula to be in a separate column as shown here within the "Query Editor". 

In other words, how would you translate this "=IF([@[Estimated Dollars]]=MAX([Estimated Dollars]),"BEST PERFORMING YEAR",IF([@[Estimated Dollars]]=MIN([Estimated Dollars]),"LEAST PERFORMING YEAR",""))" in DAX using the M language?

Fiscal Year	Estimated Dollars	Actual Dollars	Performance
1/1/2016	1.00	2	LEAST PERFORMING YEAR
1/2/2016	10.00	20
1/3/2016	20.00	21
1/4/2016	30.00	22
1/5/2016	11.00	31
1/6/2016	12.00	12
1/7/2016	13.00	13
1/8/2016	21.00	43
1/9/2016	22.00	34
1/10/2016	23.00	22
1/11/2016	33.00	14
1/12/2016	44.00	44
1/13/2016	45.00	45	BEST PERFORMING YEAR

In the second example, I want to create a look up to a certain range and return a specific result where that measure exists.

For example, if the difference between the Estimated Dollars and Actual Dollars is greater than +/_10%, return the following " Out of Tolerance". And if the difference between Estimated Dollars and Actual Dollars is within +/_10%,, return the following: "Within Tolerance" . Thirdly, If the difference is +/-10% and has a value less than +/-10.00, return the following "Review Estimate". 

Sorry this scenario is a little different than what I wrote but the challenge am having here is how to transform DAX into Power Query language. 

[Greg_Deckler]

OK, for your second revised example, you could create three custom columns in Power Query:

Percent Difference = Number.Abs(([Estimated Dollars] - [Actual Dollars]) / [Estimated Dollars])

Difference = Number.Abs([Estimated Dollars] - [Actual Dollars])

Tolerance = if [Percent Difference] > .1 then "Out of Tolerance" else if [Difference] < 10 then "Review Estimate" else "Within Tolerance"

For the first one, the MAX query, that is something that is trivial in DAX but not necessarily in "M" code. @ImkeF might have some insights into how to do that, she is the resident "M" wizard. I'll take a look but by the time I figure it out, @ImkeF will have probably already posted the answer 3 days before. I imagine it will involve something like List.Max.

[mdrammeh]

I received the following error message when I tried the percentage change formula:

"DataSource.Error: An error happened while reading data from the provider: 'The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available, and the consumer had not yet set a new Double value.'

Thanks for being patient with me. I am very new to this whole power query thing and trying to learn the hard way 🙂

[ImkeF]

Never seen an error-message like this before. Sure that's due to the percentage change formula and not somewhere before?

Have you specifically applied a number format to the respective columns before? You should do that.

[ImkeF]

🙂

if [Actual Dollars]=List.Max(NameOfPreviousStep[Actual Dollars]) then "Best performing" else if [Actual Dollars]=List.Min(NameOfPreviousStep[Actual Dollars]) then "Worst performing" else ""

The challenge here is to apply the List.Max correctly: It should look into the Actual Dollars-Column, but if you reference it within a calculated column like this: [Actual Dollars], you will only get this columns value of the current row (and not the whole column). This is a behaviour that is most often wanted for operations performed in additional columns and has therefore been made as the default setting by turning everything you paste into the editor-field into a function (that takes in the current row as it's input) (btw: if you look into the formula editor you'll see that your input into the editor is proceeded by "each" - this is a shortterm for a function)

So in order to reference a whole column within an added custom step you reference the previous step (by simply writing it's name - but don't forget the #"..." if needed), followed by the columm name in spuare brackets.

Hope this makes sense?