Power Query Data Source Paramaterization

[tschuman]

Power Query Data Source Paramaterization
Our team needs to create a workbook with 60 queries.
The Data Source for the queries will be a web data source.
The same 60 queries would be applicable across three web environments (Dev, QA, & Prod)
Example
· http://dev.mycompany.com
· http://qa.mycompany.com
· http://prod.mycompany.com
The Data source URLs contain will month and year variables
Example
· http://dev.mycompany.com?Month=JAN&Year=2015
· http://qa.mycompany.com?Month=JAN&Year=2015
· http://prod.mycompany.com?Month=JAN&Year=2015
How can we parametrize Power Query such that I can vary the environment, month and year and only maintain single 60 query workbook for all of our environments?
We are relatively new to Power Query and have not had much luck attempting this on our own.

 

[ImkeF]

In order to pass a parameter to PQ you could create a table ("Tabelle1") with one line for each of your source environment strings (call that column "Name").
Then add a column ("Choose") where you fill an "x" to the one to choose.
Then the following query would deliver the selected string/Parameter:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
FilterChoose = Table.SelectRows(Source, each [Choose] = "x"),
ResultAsText = FilterChoose{0}[Name]
in
ResultAsText

But I wonder why you go for 60 queries.
This sounds as if what you really need is a web-scraping-approach like this:

https://social.technet.microsoft.co...sheets-published-on-yahoo-finance?forum=Excel

If that's the case, you'd just have to create a dynamically changing source-table in Excel (Text-&Field-relation-concatenation).

 

[ImkeF]

Just recognized that this example doesn't have the dynamic source table-approach.
You can find an example here:
https://onedrive.live.com/edit.aspx...FF!11049&parId=DE165DDF5D02DAFF!107&app=Excel

 

[tschuman]

Just recognized that this example doesn't have the dynamic source table-approach.
You can find an example here:
https://onedrive.live.com/edit.aspx...FF!11049&parId=DE165DDF5D02DAFF!107&app=Excel

Thank You!!!!! You just saved us a tremendous amount of effort and helped prevent some RSI's.

 

[ImkeF]

Pleasure

RSI-prevention II would be this then:
Combine Multiple Excel Workbooks in Power QueryThe Ken Puls (Excelguru) Blog
This is a different approach for: Get me everyhing that's in this folder.
Enjoy