CODEWITHSUNDEEP
xmlpowerquerym
sayth
sayth

Reputation: 7048

PowerQuery M Folder import syntax for XML

I am unable to figure the correct syntax in M for getting a folder of xml files to load into my function.

These are 2 of my attempts that I cannot determine a way to resolve.

let ExcelFile = (FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    Query1 = (FileName) => [FirstResult=Meeting(FileName), SecondResult=Race(FileName), ThirdResult=Reference(FileName), FourthResult=Horses(FileName)]
in
  ExcelFile

Example 2

let  xmlImport = (FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    Query1 = (FileName) => [FirstResult=Meeting(FileName), SecondResult=Race(FileName), ThirdResult=Reference(FileName), FourthResult=Horses(FileName)],
    content = Binary.Combine(Source[Query1]),
    xmlImport = Xml.Tables(content)
in
    xmlImport

Upvotes: 1

Views: 1003

Answers (1)

Carl Walsh
Carl Walsh

Reputation: 6979

I'm not quite sure what you want to produce, but I can give a general example of what works, and a couple guesses why your Examples might be hitting errors?

Say my mashup starts like this:

let
    Source = Folder.Files("c:\code\css430\Final_Project\.idea")
in
    Source

And I want to load each cell as XML. First, I'll just click on one binary cell, and see what the UX auto-generates:

let
    Source = Folder.Files("c:\code\css430\Final_Project\.idea"),
    #"c:\code\css430\Final_Project\ idea\_misc xml" = Source{[#"Folder Path"="c:\code\css430\Final_Project\.idea\",Name="misc.xml"]}[Content],
    #"Imported XML" = Xml.Tables(#"c:\code\css430\Final_Project\ idea\_misc xml",null,1252),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported XML",{{"Attribute:version", Int64.Type}})
in
    #"Changed Type"

The interesting bit is that Xml.Tables was called on the [Content] column. I'll delete the new steps, and add a custom column just for Xml.Tables([Content]):

let
    Source = Folder.Files("c:\code\css430\Final_Project\.idea"),
    #"Added Custom" = Table.AddColumn(Source, "Xml", each Xml.Tables([Content]))
in
    #"Added Custom"

If you want to do more transformation than just call Xml.Tables, then add more steps after clicking on the one cell, copy the code, and delete the steps and put all the transformations into the Table.AddColumn function.

Only once you've got that all working, then refactor out the FilePath argument instead of the hard-coded file path.

Some things that might be going wrong in your examples:

Code like this:

(FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    Query1 = (FileName) => ...

Means you're declaring two function that each have a parameter named FileName, which is just going to be confusing later. There are rules to define which parameter gets used, but it's best to just give them different names.

Calling Xml.Tables on top of Binary.Combine will almost always get you in trouble. If you copy-paste the text of two XML files it doesn't add up to a new XML file; it's going to be a format error. Always transform XML into M tables first, then combine those tables (e.g. Table.Combine). Binary combine works OK for combining plain-text and CSV binaries, but really only those types.

The code Source[Query1] looks like it will use the Query1 function for something on the Source table, but it actually means find the "Query1" column of the table (which will cause an error). If you want to apply a transformation function to a table, Table.AddColumn is probably what you want, or maybe Table.TransformColumns

Upvotes: 1

Related Questions