How to apply same query over multiple xml files?

[cgkas]

Hi all,

I have a query that imports some specific nodes from a big nested xml file in power query for Excel. The query currently works for one xml and after parsing the XML, the ouput is placed in a worksheet. Now my issue is I have more than 600 xml files in a folder. and I would like to know how can I apply the same query to all files and append the result below the previous output in same worksheet?

My query for a single file looks like this:

let
    Source = Xml.Tables(File.Contents("D:\d\XML_Files\Inputfile_0021.xml")),
    Table1 = Source{1}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table1,{...}),
	Expand_table1 = ...
	Expand_table2 = ...
	Expand_table3 = ...
	.
	..
	...
	#"Reordered Columns" = ..
in
    #"Reordered Columns"

 Thanks in advance

[ppm1 · Accepted solution]

Try starting with the Folder connector (or better practice the SharePoint Folder connect and store the files in the cloud). Filter to just your xml files if needed, and then add a custom column with

= Xml.Tables([Content]){1}[Table]

I believe the column with the binary content is called Content (fix if not). You should then be able to expand out the data (assuming all the files have the same XML structure). If not, you can convert your original query into a function (with the binary content as the input), and use that instead.

Pat