Automate downloading of multiple xml files from web service with power query

Question

I want to download multiple xml files from web service API. I have a query that gets a JSON document: = Json.Document(Web.Contents("http://reports.sem-o.com/api/v1/documents/static-reports?DPuG_ID=BM-086&page_size=100"))

and manipulates it to get list of file names such as: PUB_DailyMeterDataD1_201812041627.xml in a column on an excel spreadsheet.

I hoped to get a function to run against this list of names to get all the data, so first I worked on one file: PUB_DailyMeterDataD1_201812041627

= Xml.Tables(Web.Contents("https://reports.sem-o.com/documents/PUB_DailyMeterDataD1_201812041627.xml"))

This gets an xml table which I manipulate to get the data I want (the half hourly metered MWh for generator GU_401970

Now I want to change the query into a function to automate the process across all xml files avaiable from the service. The function requires a variable to be substituted for the filename. I try this as preparation for the function:

let Filename="PUB_DailyMeterDataD1_201812041627.xml",

Source = (Web.Contents("https://reports.sem-o.com/documents/Filename")), (followed by the manipulating Mcode)

This doesnt work.

then this:

let Filename="PUB_DailyMeterDataD1_201812041627.xml",

Source = Xml.Tables(Web.Contents("https://reports.sem-o.com/documents/[Filename]")),

I get: DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: Data at the root level is invalid. Line 1, position 1.) Details: Binary

So stuck here. Can you help. thanks Conor

If = Xml.Tables(Web.Contents("reports.sem-o.com/documents/…)) returns an xml file why does: let Filename="PUB_DailyMeterDataD1_201812041627.xml", Source = Xml.Tables(Web.Contents("reports.sem-o.com/documents/[Filename]")), return an error? — Conor

Wedge Wedge · Accepted Answer · 2019-01-17T23:51:44

You append strings with the "&" symbol in Power Query. [Somename] is the format for referencing a field within a table, a normal variable is just referenced with it's name. So in your example

let Filename="PUB_DailyMeterDataD1_201812041627.xml",

Source = Xml.Tables(Web.Contents("https://reports.sem-o.com/documents/" & Filename)),

Would work.

It sounds like you have an existing query that drills down to a list of filenames and you are trying to use that to import them from the url though, so assuming that the column you have gotten the filenames from is called "Filename" then you could add a custom column with this in it

Xml.Tables(Web.Contents("https://reports.sem-o.com/documents/" & [Filename]))

And it will load the table onto the row of each of the filenames.

Automate downloading of multiple xml files from web service with power query

1 Answers