Web based XML Query dynamic values

[asjam86]

Hi all,

We run a 3rdparty system which I can get information from via XML as well as the GUI. I would like to use this to bring in information into PowerBi. The way I access the data is through an XML query string.

https://3rdpartyapp.com/API-Token=123456?QUERYSTRING

I would like to obtain information about a customers address I need to input the clientID...

https://3rdpartryapp.com/API-Token=123456?Client_Address&ClientID=09876

The problem is I want this information for all my clients at the same time and not choose 1 at a time. I looked at using parameter query and where by I could update the query being run to return by results what I would like to do is run the query against all ClientID's which I have stored in another dataset in PowerBi. 

Any help would be appricated as I'm sure my brain is turning a slight yellow and black colour. 

Many thanks

Justin

[Anonymous]

You can do this with some M. You need to start with a list of your client IDs, and from there you can do a simple List.Transform to iterate out all the URLs you want to hit.

For example, if I have:

Identifiers = 
    let
        x = {1..20}
    in
        x;

Test = 
    let
        Source = List.Transform(Identifiers, 
            each "http://api.population.io/1.0/population/1980/" & "United States" & "/" & Text.From(_) & "/")
    in
        Source;

Calling "Test" will show a list of the URLs:

From here, I know we can call Json.Document(Web.Contents(url)) on each one, (or Xml.Document() in your case). So in Power BI, I can use the Advanced Editor feature to create this. On both of these tables, I'm starting with "Blank Query", but you might already have your Client IDs. Make sure you isolate those to a List, rather than a table.

I used the GUI to "cheat" here and autogenerate those last 3 lines to extract the values out of the JSON objects

I hope this sets you down the right track. Basically, you're looking to generate URLs and just pass each one to a Web content function, and then an XML parsing function.