Results 1 to 3 of 3

Thread: Formula for power query

  1. #1

    Formula for power query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a custom column in power query that I need to check if one of these values 1, 25, 31, 2, 8, 19 is in another column on same row and if True then return a value from another column same row. I currently use this formula " =IF(OR(I2 = {1,25,31,2,8,19} ),M2,"0") " in excel but would like it to be added in power query for 3 different columns that use different sets of values. I have tried modifying the formula to work with power query but it does not recognize the OR part. Would anyone have suggestion on how to make this work. Thanks

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    You have to test them all

    if [col_name]=1 or [col_name]=2 or [col_name]=8 or [col_name]=19 or [col_name]=25 or [col_name]=31 then 0 else ""

    or maybe you could use a list to be more Excel-like

    if List.Contains({1, 2, 8, 19, 25, 31}, [col_name]) then 0 else ""

  3. #3
    This worked great, changed to if List.Contains({1,2,8,19,25,31}, [col_name]) then [col_name] else "0". Thanks so much for help.

    if List.Contains({1, 2, 8, 19, 25, 31}, [col_name]) then 0 else ""[/QUOTE]
    Last edited by Turbo; 2015-04-02 at 03:58 PM.

Posting Permissions

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