Power Query Function To Process Several XML (Online) Files

Creating a function in Power Query is even easier than previously and will help us processing several files following the steps we make for only one file.

Let’s consider the following use case: automatically download and process data from several XML files posted online (files with identical structure or with a similar one – in case we are able to create a generic procedure that is able to cope with a certain amount of variance in the files structure, e.g. remove blank rows, regardless of their number).

The table below contains links to XML files with historical exchange rates from National Bank of Romania (NBR).

Note: double click on a cell to see formulas. See queries, formulas and settings: download file.

<h2>1. Downloading and processing a single XML file</h2>

If you download the .xlsx file above you will find a query named xml_oneyr_online, with the steps and the code below (for the M code/ query code, in the Query Editor, go to Home/ Advanced Editor). At the beginning, the query source was a simple string with the link to the XML file (http://bnr.ro/files/xml/years/nbrfxrates2017.xml). In the Excel file you will find a modified source, in order to take into account the recommendation below (after creating the query with a static source, we will adjust the source to become dynamic).

Recommendation: For your convenience (and especially if you prepare the file for somebody not familiar with Power Query) start with creating a parameter fed by a query, which means avoiding intervention in code when the link to the XML file is modified. Users will only modify the link in a cell and then hit the refresh button of a query. This will also help automating the process.

Create a new query (named link_full) to pass the link of each XML file (link column in the Excel table) to a parameter and through the parameter to the function. See the new query steps in the Excel file. Remember to convert the column returned by the query into a list (select the query column and go to Transform/Convert to list).  The new query will provide a list with links for each XML file, ready to be sent to the parameter:

let
    Source = Xml.Tables(Web.Contents("http://bnr.ro/files/xml/years/nbrfxrates2017.xml")),
    Table1 = Source{1}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table1,{"Cube"}),
    #"Expanded Cube" = Table.ExpandTableColumn(#"Removed Other Columns", "Cube", {"Rate", "Attribute:date"}, {"Cube.Rate", "Cube.Attribute:date"}),
    #"Expanded Cube.Rate" = Table.ExpandTableColumn(#"Expanded Cube", "Cube.Rate", {"Element:Text", "Attribute:currency", "Attribute:multiplier"}, {"Cube.Rate.Element:Text", "Cube.Rate.Attribute:currency", "Cube.Rate.Attribute:multiplier"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Cube.Rate", "Date", each Date.FromText([#"Cube.Attribute:date"])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Cube.Rate.Attribute:currency", "Currency"}, {"Cube.Rate.Attribute:multiplier", "Multiplier"}, {"Cube.Rate.Element:Text", "Rate"}}),
    #"Filtered ""-"" (Rate)" = Table.SelectRows(#"Renamed Columns", each [Rate] <> "-"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered ""-"" (Rate)",{{"Date", type date}, {"Multiplier", Int64.Type}, {"Rate", type number}})
in
    #"Changed Type"

‍

‍

‍

‍