Power Query

Question

Power Query

Oleksandr Iakovenchuk 0

Hi, pls help to rewrite Excel formula for Power Query

I have found that for power query I need to use then and else instead of comma

I got an error "token Eof expected", place of the error I have highlighted in bold "" else

Excel formula: =if(ISERROR((G6-F6)/F6100),"",IF((G6-F6)/F6100>999,"",IF((G6-F6)/F6100<-999,"",IF(AND(G6>0,F6>0),(G6-F6)/F6100,(1)(G6-F6)/F6100))))

Power query: =if(ISERROR((G6-F6)/F6100) then "" else IF((G6-F6)/F6100>999 then "" else IF((G6-F6)/F6100<-999 then "" else IF(AND(G6>0,F6>0),(G6-F6)/F6100,(1)(G6-F6)/F6100))))

Barry Schwarz 3,746 Reputation points

2024-03-29T17:07:26.6766667+00:00
All your IF clauses in the Power query have a then and an else except the last which has neither. Could this be part of your problem?

In both the Excel formula and the Power query, the last IF seems malformed:

The condition expression is AND(G6>0,F6>0) which seems OK.

The true value is (G6-F6)/F6100 which also seems OK.

The false value is (1)(G6-F6)/F6100 which appears to be invalid. What is the purpose of the (1)? The rest of the expression is identical to the true value. Why have an IF with the same values for true and false?

I don't know Power query but in the Excel formula, it might be easier to use the IFS function instead of a set of nested IF functions.
Oleksandr Iakovenchuk 0 Reputation points

2024-03-29T21:58:12.5933333+00:00

Barry, thanks a lot for your reply.

I do not know why it is used (1), the formula was written not by me.

"Why have an IF with the same values for true and false?" - I do not know. Maybe (1) make it different.
Barry Schwarz 3,746 Reputation points

2024-03-30T00:00:09.2233333+00:00

Then you probably need to talk to the author. If you copied this from the internet, it should come as no surprise that there is a lot of broken code out there.

1 answer

Your answer

Barry Schwarz 3,746 Reputation points

2024-03-29T17:07:26.6766667+00:00

All your IF clauses in the Power query have a then and an else except the last which has neither. Could this be part of your problem?

In both the Excel formula and the Power query, the last IF seems malformed:

The condition expression is AND(G6>0,F6>0) which seems OK.

The true value is (G6-F6)/F6100 which also seems OK.

The false value is (1)(G6-F6)/F6100 which appears to be invalid. What is the purpose of the (1)? The rest of the expression is identical to the true value. Why have an IF with the same values for true and false?

I don't know Power query but in the Excel formula, it might be easier to use the IFS function instead of a set of nested IF functions.
Oleksandr Iakovenchuk 0 Reputation points

2024-03-29T21:58:12.5933333+00:00

Barry, thanks a lot for your reply.

I do not know why it is used (1), the formula was written not by me.

"Why have an IF with the same values for true and false?" - I do not know. Maybe (1) make it different.
Barry Schwarz 3,746 Reputation points

2024-03-30T00:00:09.2233333+00:00

Then you probably need to talk to the author. If you copied this from the internet, it should come as no surprise that there is a lot of broken code out there.

Answer 1

Hi,

It looks like the formula is intended to calculate variance/growth between two values with some added logic for errors, negative values and results over 999. If my assumption is correct then I think your formula is missing two things:

an asterisk between F6 and 100
the (1) should be (-1)

For Excel:

In Excel, you can use a SWITCH(TRUE()...) pattern instead of nested if statements and wrap that in an IFERROR. This might make it easier for you to read/interpret.

User's image

Excel Formula
=
IFERROR(
     SWITCH(
          TRUE(),
               (G6-F6)/F6*100 > 999, "",
               (G6-F6)/F6*100 < -999, "",
               AND(G6>0,F6>0), (G6-F6)/F6*100,
               ((G6-F6)/F6*100)*(-1)),
     "")

For Power Query:

In PQ, write the if/else statements and wrap them in a try/otherwise for the error handling.

User's image

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUs7TT1TSUTI1UIrViVYywc40RbB1kTlgti6KDJgXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value1 = _t, Value2 = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Value1", type number}, {"Value2", type number}}),
    AddCol = Table.AddColumn(PreviousStep, "Result",  each try
      if ([Value2]-[Value1])/[Value1]*100 > 999 then "" else
      if ([Value2]-[Value1])/[Value1]*100 < -999 then "" else
      if ([Value1]>0 and [Value2]>0) then ([Value2]-[Value1])/[Value1]*100 else
      (([Value2]-[Value1])/[Value1]*100)*(-1) otherwise "", type number)

in
AddCol

Share via

Power Query

1 answer

For Power Query:

Your answer