+ Reply to Thread
Results 1 to 9 of 9

Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    73

    Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    To all,

    Thank you to all XL gurus that help, assist, teach inside this forum.

    Today, I would like to be able to monitor Efforts for any project I am working on.

    So far, I have managed to extract data via Power Query but I am struggling with setting up following parameters to make my query dynamic based on

    Start date:
    End date:
    Company Id:
    Project Id:

    URL: (Part of URL has been replaced by site for confidential reason.)

    Please Login or Register  to view this content.
    Advanced Editor:

    Please Login or Register  to view this content.
    Any guidance, assistance will be highly appreciated

    Dude6571
    Last edited by dude6571; 08-15-2019 at 12:43 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,505

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    You can use custom function to supply values like below.
    EDIT: You may want to edit your "M" and replace the site name. I think you missed it there.
    Please Login or Register  to view this content.
    0.JPG
    Last edited by CK76; 08-15-2019 at 12:35 PM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,078

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    Create a two-column table in Excel, header values of Name & Value, and row values of Start Date and 01/08/2019, End Date and 31/12/2019, Project Id and 339731, and call the table Parameters

    Then modify the query like so

    Please Login or Register  to view this content.
    Last edited by Bob Phillips; 08-15-2019 at 01:04 PM.

  4. #4
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    73

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    I will check tomorrow when back at work and let you know. Thank you

  5. #5
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    73

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    Hi Bob,

    Thank you for your reply.

    Having issue with "Token Comma expected" Show error => in (bottom of the code)

    Please Login or Register  to view this content.
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    73

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    Hi CK76,

    Thank you for your reply,

    Having issue with getting correct table to get data from

    Please Login or Register  to view this content.
    Attached Images Attached Images

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,505

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    GetData should be invoked at start (i.e. it replaces source). Just invoke it directly from the function and perform transformation on the generated table.

    Alternately, you can add all the transformation steps into the custom function.


    EDIT: If you have list of parameters in a table, you can invoke it by supplying table columns as parameter values for the function.
    Last edited by CK76; 08-16-2019 at 09:13 AM.

  8. #8
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,078

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    I missed off a closing bracket.

    BTW, it looks as though you have some more parameter values to add there.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    73

    Re: Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters

    Hi Bob,

    Thank you.

    I will now work on the different parameters as you have suggested.

    Thank you again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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