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

[dude6571]

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

[CK76]

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

[Bob Phillips]

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.

[dude6571]

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

[dude6571]

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.

[dude6571]

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.

[CK76]

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.

[Bob Phillips]

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.

[dude6571]

Hi Bob,

Thank you.

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

Thank you again