Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code

[jannie]

Chris Webb wrote this great blog:

https://blog.crossjoin.co.uk/2021/01/10/handling-multiple-url-query-parameters-with-the-same-name-us...

I have 3 parameters in my Power BI Service Dataflow. I manually addede the parameters to the M-Code and the query works, until I make a function of it whereby I pass multiple parameters to it then I cannot save the dataflow, and get the following error: 

Can't save dataflow

One or more tables references a dynamic data source.

My code looks as follows:

"Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & prmTicker & "?period1=" & prmStartDate & "&period2=" & prmEndDate & "&interval=1d"), [Delimiter = ",", Columns = 7, QuoteStyle = QuoteStyle.None])"

Does anyone know how to convert this to Chris' format, which looks something like this: 

"Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId={"1","2"}]])"

Thanks

Jannie

[jpessoa8 · Accepted solution]

Hi @jannie ,

Since the ticker is not a parameter in the Yahoo Finance API (like period1, period2 and interval are) you cannot follow the approach of [Query=[...

Instead, you can used directly the RelativePath approach :

let
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/", [RelativePath=prmTicker & "?period1="& prmStartDate & "&period2=" & prmEndDate & "&interval=1d"]),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

For documentation, if ticker was a parameter and you wanted to use the [Query=[... approach it will be like this:

let
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/TSLA", [Query=[ticker = prmTicker, period1= prmStartDate, period2= prmEndDate, interval="1d"]]),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[jannie]

Excellent thanks Jorge it works perfectly and I now understand the syntax.

All the best

Jannie