Picking up multiple xml files on JSON web service

[conor]

Hi
I have 2 copies of M is for data monkey its so good and it helped me as below but now stuck. Help if you can working file is attached


I want to get multiple xml files from web service API. The query in the attached excel sheet, the uninspiringly named: static-reports?DPuG_ID=BM-086&page_size=50 gets the 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

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 as follows in the equally uninspiring query name: PUB_DailyMeterDataD1_201812041627


= Xml.Tables(Web.Contents("https://reports.sem-o.com/documents/PUB_DailyMeterDataD1_201812041627.xml"))
gets an xml table and is easily manipulated to get the data I want (the half hourly metered MWh for generator GU_401970

So far so good; Now I want to change the manipulation into a function to automate the process across all xml files avaiable from the service and 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)
no good

then this:

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

no good:
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 pleeeeeeeeeeease.
thanks
Conor

 

[conor]

Solved it myself:
You must concatenate the text to the web address string, so:
filename as text
Source = (Web.Contents("https://reports.sem-o.com/documents/"&filename)),

Hey presto!