+ Reply to Thread
Results 1 to 31 of 31

Power Query code to test for multiple conditions

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Power Query code to test for multiple conditions

    I'm just getting into Power Query and I'm having trouble with the following.

    I have a line of code;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    At the moment this just checks one column [Net Billings (exc GST)] for the value "NSW".

    I want to modify this code to check for any of the Australian states/territories (ACT,NSW,QLD,WA,SA,NT,VIC,TAS) in that column, but also check if the value contained in the column [Item #] is a number.

    In English - If the value in "Net Billings (exc GST)" is one of the states AND the value in "Item #" is a number then return the value in "Net Billings (exc GST)" else "null"

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Could you attach a sample workbook with PQ as you currently have it?

    We need a power section here - I have asked for it ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Sure. Here it is.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Thanks. I can't see anything that looks like a number in the Item # column - it's all text or null values. Am I missing something?

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    For example B37 has the value 71.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    OK. You might want to start here:

    https://community.powerbi.com/t5/Des...OR/td-p/233010

    You can add extra IF conditions by editing your conditional column (using the cog wheel), but if you want to add an AND condition, you will need to do that manually, I think.

    Also (from Stack Overflow):

    IsNumber =Value.Is(Value.FromText([ColumnOfMixedValues]), type number)

    IsText =Value.Is(Value.FromText([ColumnOfMixedValues]), type text)
    Last edited by AliGW; 07-17-2018 at 04:18 AM.

  7. #7
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Thanks Ali for the pointer to the Power BI forum, I'm no longer at work so will check it out tomorrow.

    I found that code, but I'm not sure of the syntax of the AND statement.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    If I get a moment later, I'll see if I can adapt it for you. Just working on another job at the moment, though.

  9. #9
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Thanks Ali

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    OK. I had a quick play with the M code and got this:
    Please Login or Register  to view this content.
    It seems to do what you want. I'm learning PQ, too, so did this by Googling and a bit of trial and error.

    Syntax: if criterion = "value" and (criterion = "value" or criterion = "value" or criterion = "value") then return_value else null
    Attached Files Attached Files
    Last edited by AliGW; 07-17-2018 at 05:48 AM.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Power Query code to test for multiple conditions

    I don't know what are you tryin' to achieve but you can try with defined list of states then filter items by null
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by sandy666 View Post
    I don't know what are you tryin' to achieve ...
    The objective was clearly stated in the opening post, Sandy:

    I want to modify this code to check for any of the Australian states/territories (ACT,NSW,QLD,WA,SA,NT,VIC,TAS) in that column, but also check if the value contained in the column [Item #] is a number.

    In English - If the value in "Net Billings (exc GST)" is one of the states AND the value in "Item #" is a number then return the value in "Net Billings (exc GST)" else "null"

  13. #13
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by AliGW View Post
    Syntax: if criterion = "value" and (criterion = "value" or criterion = "value" or criterion = "value") then return_value else null
    Awesome, thanks Ali.

  14. #14
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by sandy666 View Post
    I don't know what are you tryin' to achieve but you can try with defined list of states then filter items by null
    Hi Sandy,

    Not exactly, but I see what you did there.

    Thanks

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    You're welcome! And I learnt another little Power Query trick today, too, so thank you.

    EDIT: Thanks for the rep.
    Last edited by AliGW; 07-18-2018 at 04:10 AM.

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Power Query code to test for multiple conditions

    Since you're matching to a list, you could use

    Please Login or Register  to view this content.
    instead of all the Or conditions.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Yes, but that does not include the AND criterion, Rory - in your example, how would that be done?

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Power Query code to test for multiple conditions

    You'd just replace the And ... () or () or () in your code with what I have there:

    Please Login or Register  to view this content.
    becomes:

    Please Login or Register  to view this content.

  19. #19
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by rorya View Post
    Since you're matching to a list, you could use

    Please Login or Register  to view this content.
    instead of all the Or conditions.
    I've just tried this but it's saying there is an error in the formula;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    So this:

    Please Login or Register  to view this content.
    becomes this:

    Please Login or Register  to view this content.
    Is this correct?

  21. #21
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by rorya View Post
    You'd just replace the And ... () or () or () in your code with what I have there:
    Great work rorya. It works!!!

  22. #22
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Quote Originally Posted by AliGW View Post
    Is this correct?
    Correct Ali

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    I tried this:
    Please Login or Register  to view this content.
    No error, but it is returning nothing at all in the destination column - all null.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Not working here - can you post the exact code you now have working, please?

  25. #25
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This works for me.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    No, that's not what I meant - please copy and paste here everything from the advanced editor window - the entire M code for the query. Thanks!

  27. #27
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Never mind - I'll work it out for myself. That M code is much longer than the M code I produced in the sample file. Thanks, anyway.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Got it working now - thanks both of you!

  30. #30
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query code to test for multiple conditions

    Yes I've added some changes, that's why I didn't paste the entire thing earlier.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query code to test for multiple conditions

    Happy days - I've got it in my sample sheet now. Love learning PQ!

+ 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. 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
  2. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  3. Does My VBA Code To Refresh CSV Power Query Connections Need To Change for MySQL Queries?
    By beaglesBuddy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2017, 02:57 PM
  4. [SOLVED] Multiple tasks on tables in Power Query
    By wrybel in forum Excel General
    Replies: 11
    Last Post: 08-01-2017, 03:17 AM
  5. code to refresh power query not working ..
    By Hassan1977 in forum Excel General
    Replies: 0
    Last Post: 08-31-2016, 12:31 PM
  6. VBA code required to run after checkbox value is true and test multiple conditions.
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2015, 08:30 PM
  7. Using Power Query to do multiple searches on a website
    By eodsolo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-11-2014, 09:33 PM

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