+ Reply to Thread
Results 1 to 6 of 6

Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

  1. #1
    Registered User
    Join Date
    04-25-2018
    Location
    Nashville, TN
    MS-Off Ver
    2016
    Posts
    34

    Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    Hello,

    I am attempting to add a custom logical column in an excel power query. The new column should be a 0 or 1 based on whether the number in columns a or b are less than or greater than 1. Attached below is a spreadsheet with some data to explain what I am trying to achieve. With the code below i am able to get the column I desire, however, when there is a null value in column A, and the corresponding cell in column b does have a value, the calculated cell in the custom column results in an error. I would like the formula to ignore cells that are null and calculate using the non-empty cell. For instance, cell in column a is null, cell in column b is 1.5 my code below throws an error, but i would like it to return a value of 1 for being greater than 1.

    Please Login or Register  to view this content.

    Thank you in advance.
    Attached Files Attached Files
    Last edited by jekeith; 05-15-2018 at 10:10 AM. Reason: for clarity

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    This seems to work:

    Please Login or Register  to view this content.
    Keep in mind that I am new to Power Query so there is likely a more efficient way.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-25-2018
    Location
    Nashville, TN
    MS-Off Ver
    2016
    Posts
    34

    Re: Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    Understood. Thanks 63Falcondude. There won't ever be a large amount of data to calculate over for this, so I'd say this will be fine for my uses.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    You're welcome. Happy to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    Hi jekeith.

    Here is my example:
    PQ Column a OR Column b.xlsx
    and explanation at:
    https://www.excelguru.ca/blog/2014/0...n-power-query/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

    Try something like below...
    Please Login or Register  to view this content.
    0.JPG

    =try ... otherwise

    can be used like error handling in PowerQuery.

    Alternately, you could test if field is numeric.
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

+ 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: 4
    Last Post: 04-26-2018, 11:35 AM
  2. [SOLVED] Insert a date value as a Custom Column in Power Query
    By heytherejem in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-28-2018, 12:42 PM
  3. Power Query - Invoke Custom Function
    By leukep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2017, 02:18 PM
  4. Unpivot in Power Query keeping two columns
    By PennyK in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-10-2017, 06:27 AM
  5. Return closest value less than given number matching another column - Power Query ?
    By ChipsSlave in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-29-2017, 03:16 PM
  6. Help with Power Query Custom Column Formula
    By travis.cook21 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2016, 01:45 PM
  7. How to remove null values from several columns with Power Query?
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2016, 11:08 AM

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