Web.Contents Assistance with converting URL to PowerQuery

[Anonymous]

Hi,

I have here a very complex API call that I need to do using Power BI. 

https://wej51919.live.dynatrace.com/api/v2/metrics/query?metricSelector=(builtin:synthetic.browser.event.actionDuration.xhr:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.actionDuration.load:splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.availability.location.total:splitBy("dt.entity.synthetic_test"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.errorCodes.geo:splitBy("By error code"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.total:splitBy("dt.entity.synthetic_test"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.success:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"Loading of ~~~"https://www.merchantstotalview.com/~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log Out~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.failure.geo:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"Loading of ~~~"https://www.merchantstotalview.com/~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log Out~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names&from=2023-04-13 09:25&to=2023-05-04 09:25&resolution=Inf

As you can see, the URL has 4 query parameters named metricSelector, from, to, resolution (check the url above).

I am having trouble building the query to connect as it is too complex for me. I have the following code atm, as you can see I am using the RelativePath and Query options.

let
    Source = (Web.Contents(
        "https://wej51919.live.dynatrace.com/api/v2/metrics/",
        [
            RelativePath = "query",
            Query = [
                metricSelector="(builtin:synthetic.browser.event.actionDuration.xhr:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.actionDuration.load:splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.availability.location.total:splitBy("dt.entity.synthetic_test"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.errorCodes.geo:splitBy("By error code"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.total:splitBy("dt.entity.synthetic_test"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.success:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"Loading of ~~~"https://www.merchantstotalview.com/~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log Out~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names,(builtin:synthetic.browser.event.failure.geo:filter(and(or(in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log In~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"Loading of ~~~"https://www.merchantstotalview.com/~~~"~")")),in("dt.entity.synthetic_test_step",entitySelector("type(synthetic_test_step),entityName.equals(~"click on ~~~"Log Out~~~"~")"))))):splitBy("dt.entity.synthetic_test_step"):sort(value(auto,descending)):limit(20)):names",

                from="2023-04-13 09:25",
                to="2023-05-04 09:25",
                resolution="Inf"
            ]
        ],

But if I insert the whole content of the metricSelector parameter from the URL, I am having this error below.

This is my first time using this as a datasource so I'm kinda stuck here. I don't know if I need to use escape in the parameter as it has too many "" or am I building it the wrong way? I need your expert advice here. Thanks in advance.

[lbendlin]

yes, you need to encode your URI to avoid conflicts.

Uri.BuildQueryString - PowerQuery M | Microsoft Learn