Custom Column in PQ
Custom Column in PQ
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Custom Column in PQ

  1. #1
    Board Regular
    Join Date
    Oct 2004
    Location
    Dammam, KSA
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Custom Column in PQ


    Custom Column in PQ
    Hi,

    can anyone please tell what is wrong in this

    if ([DeliveryTime]-[SUBMISSION_TIME])<0 then [DeliveryTime]-[SUBMISSION_TIME]+1 else [DeliveryTime]-[SUBMISSION_TIME]
    sadyboy

  2. #2
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Column in PQ

    Assuming that your fields are of type time: if you subtract times, the result is a duration. So if you want to compare the result with a value, you need to use #duration , like this:

    Code:
    =if ([DeliveryTime]-[SUBMISSION_TIME])<#duration (0,0,0,0) then [DeliveryTime]-[SUBMISSION_TIME]+#duration (1,0,0,0) else [DeliveryTime]-[SUBMISSION_TIME]
    Last edited by MarcelBeug; Nov 30th, 2017 at 03:52 AM.
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Column in PQ

    Alternative solution:

    Code:
    =#time(0,0,0) + ([DeliveryTime] - [SUBMISSION_TIME]) - #time(0,0,0)
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  4. #4
    Board Regular
    Join Date
    Oct 2004
    Location
    Dammam, KSA
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Column in PQ

      
    Thank You..
    sadyboy

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com