+ Reply to Thread
Results 1 to 6 of 6

Power Query nested if evaluation order

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    620

    Power Query nested if evaluation order

    Hi,
    I have a question re the order of using nested if's in PQ,

    I want to make 'numbers" = 1000 and text strings thant start with 'a' = "AAA , if first tried this,

    Does not work;

    Please Login or Register  to view this content.
    The first if does but I get an error for numbers, so I changed the order and this works

    Works;
    Please Login or Register  to view this content.
    I can't see why they outcome should differ? I have tried by putting an if to text if it is text before taking the first value but still no good,

    Please Login or Register  to view this content.
    Can someone point out the evaluation order ?

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,247

    Re: Power Query nested if evaluation order

    The first one doesn't work because the first thing you try is a Text.Start which will fail with a number.

    Your last one has the else if in the wrong place - you currently have it within the 'if value.type(x) = type text' sectiopn, so it can't be type number. You need:

    Please Login or Register  to view this content.
    the order of operations is the same as it would be in any language I can think of. Your third version might be more obvious if you indent it:

    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    620

    Re: Power Query nested if evaluation order

    Thanks, re the order yes I argued it through in my head and sort of see what you mean,

    but then thought starts with = fail = otherwise try this if, so just ended up confursed.

    RD

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,247

    Re: Power Query nested if evaluation order

    Quote Originally Posted by Dicken View Post
    but then thought starts with = fail = otherwise try this if
    You'd need a try statement for that to work.

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    620

    Re: Power Query nested if evaluation order

    Thanks saying that in my head does make sense,

    RD

    Would give rep but won't allow.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,247

    Re: Power Query nested if evaluation order

    Quote Originally Posted by Dicken View Post
    Would give rep but won't allow.
    Not to worry - I appreciate the thought!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-14-2023, 10:15 AM
  2. Replies: 1
    Last Post: 03-05-2023, 06:55 AM
  3. Power Query refresh order
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2021, 09:58 AM
  4. Power query changes line order
    By afgi in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-29-2020, 05:55 PM
  5. [SOLVED] Power Query - how to keep order of the source table
    By afgi in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 02-26-2020, 02:59 AM
  6. Power Query Refresh Order
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2019, 11:02 PM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1