+ Reply to Thread
Results 1 to 2 of 2

Power Query: Invoke custom function on several pairs of replaceable columns

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Power Query: Invoke custom function on several pairs of replaceable columns

    Hi,

    I am working on a project in Power Query and want to keep it dynamic and efficient. I have a custom function that I want to invoke on column1 (StartDate) and column2 (EndDate) to calculate duration between datetimes. Further, I want to invoke it on column2 (StartDate) and column3 (EndDate) etc. until the last column (StartDate), which I imagine would have to be paired with a fictional last+1 column of zeroes (EndDate).

    The number of columns and their names will change from time to time, but the order will be as needed (column1, column2, column3 etc. to be calculated as described above).

    I am looking to having this operation done as a single step, and ideally naming the new calculated columns the same as the starter column (so that the first calculation between the position column1 and position column2 will have the same name as column1 etc., either with an added "duration_" suffix or prefix, or having the new columns copied to a new query with identical names as the ones used for calculating the durations).

    I am not sure if this is relatively easy to achieve or not, but I have attached an example workbook including one calculated column called "01_duration".

    Best regards,
    Marbleking
    Last edited by Marbleking; 11-12-2021 at 10:41 AM.

  2. #2
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Power Query: Invoke custom function on several pairs of replaceable columns

    Hi,

    I tried the steps described in this post, courtesy of Imke Feldmann and Cameron Wallace:

    https://www.thebiccountant.com/2019/...d-power-query/

    I couldn't wrap my head around it to get it to work, though. I was trying to use the custom function "TRANSFORMATION" to run the custom function "NETWORKDAYS" across several pairs of columns, see attached.

    Anyone else able to?

    Regards,
    Marbleking

+ 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. [SOLVED] Power query custom properties
    By DystopiaRelic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2020, 08:39 AM
  2. [SOLVED] Custom columns in Power query(uniqe ID )
    By hudson andrew in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-09-2020, 08:57 AM
  3. Add custom rows and columns in Power Query
    By Shao85 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-10-2019, 06:02 AM
  4. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  5. Power Query Custom Column Formula
    By whiskeybravo91 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2018, 09:57 AM
  6. [SOLVED] Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1
    By jekeith in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2018, 10:31 AM
  7. Power Query - Invoke Custom Function
    By leukep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2017, 02:18 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