The way I usually do it is with 2 queries: a folder query to get the paths/names of the files, and a function query to parse them.
Steps:
- Query the folder where your XML files are
- Add a column that combines the path and file name to make an absolute path to the XML file and call it [Path]
- Create an XML query to one of the XML files in the folder
- Open advanced editor and create a parameter by adding (path as text) => at the top
- Replace the path specified inside Folder.Files() to path
- Close the advanced editor and name the query "getXML"
- In the Folder Query, add a custom column and call the getXML function by stating getXML([Path])
- Close and Load
XML Parse Function Query
let
Source = Folder.Files("C:\XML Files"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Path", each [Folder Path] & [Name]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "XML Data", each getXML([Path])),
#"Expanded XML Data" = Table.ExpandTableColumn(#"Added Custom1", "XML Data", {"TITLE", "ARTIST", "COUNTRY", "COMPANY", "PRICE", "YEAR"}, {"TITLE", "ARTIST", "COUNTRY", "COMPANY", "PRICE", "YEAR"})
in
#"Expanded XML Data"Folder Query
(path as text) =>
let
Source = Xml.Tables(File.Contents(path)),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"TITLE", type text}, {"ARTIST", type text}, {"COUNTRY", type text}, {"COMPANY", type text}, {"PRICE", type number}, {"YEAR", Int64.Type}})
in
#"Changed Type"
Message me if you would like an example PBIX file that does this
Alex
Alex Potter
Lead Visualization Engineer
Luxoft Inc.
