none
Combine 4 XML Queries on same source to be a global folder query RRS feed

  • Question

  • How can I create a global query for XML so i can parse a directory of XML files?

    It takes 4 queries to parse the tabular data in the XML in and keep the relations, is there a way to parameterize these queries so I can perform this repeatably?

    Query1

    let
        Source = Xml.Tables(File.Contents("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Attribute:id", "Attribute:venue", "Attribute:date", "Attribute:trackcondition"})
    in
        #"Removed Other Columns"

    Query 2

    let
        Source = Xml.Tables(File.Contents("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"club", "race", "Attribute:id"}),
        #"Expanded race" = Table.ExpandTableColumn(#"Removed Other Columns", "race", {"condition", "nomination", "Attribute:id", "Attribute:number", "Attribute:nomnumber", "Attribute:division", "Attribute:name", "Attribute:mediumname", "Attribute:shortname", "Attribute:stage", "Attribute:distance", "Attribute:minweight", "Attribute:raisedweight", "Attribute:class", "Attribute:age", "Attribute:grade", "Attribute:weightcondition", "Attribute:trophy", "Attribute:owner", "Attribute:trainer", "Attribute:jockey", "Attribute:strapper", "Attribute:totalprize", "Attribute:first", "Attribute:second", "Attribute:third", "Attribute:fourth", "Attribute:fifth", "Attribute:time", "Attribute:bonustype", "Attribute:nomsfee", "Attribute:acceptfee", "Attribute:trackcondition", "Attribute:timingmethod", "Attribute:fastesttime", "Attribute:sectionaltime", "Attribute:formavailable", "Attribute:racebookprize"}, {"race.condition", "race.nomination", "race.Attribute:id", "race.Attribute:number", "race.Attribute:nomnumber", "race.Attribute:division", "race.Attribute:name", "race.Attribute:mediumname", "race.Attribute:shortname", "race.Attribute:stage", "race.Attribute:distance", "race.Attribute:minweight", "race.Attribute:raisedweight", "race.Attribute:class", "race.Attribute:age", "race.Attribute:grade", "race.Attribute:weightcondition", "race.Attribute:trophy", "race.Attribute:owner", "race.Attribute:trainer", "race.Attribute:jockey", "race.Attribute:strapper", "race.Attribute:totalprize", "race.Attribute:first", "race.Attribute:second", "race.Attribute:third", "race.Attribute:fourth", "race.Attribute:fifth", "race.Attribute:time", "race.Attribute:bonustype", "race.Attribute:nomsfee", "race.Attribute:acceptfee", "race.Attribute:trackcondition", "race.Attribute:timingmethod", "race.Attribute:fastesttime", "race.Attribute:sectionaltime", "race.Attribute:formavailable", "race.Attribute:racebookprize"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Expanded race",{"club", "race.Attribute:id", "race.Attribute:number", "race.Attribute:name", "race.Attribute:stage", "race.Attribute:distance", "race.Attribute:age", "race.Attribute:weightcondition", "race.Attribute:time", "race.Attribute:trackcondition", "race.Attribute:fastesttime", "race.Attribute:sectionaltime", "Attribute:id"})
    in
        #"Removed Other Columns1"

    Query 3

    let
        Source = Xml.Tables(File.Contents("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"club", "race", "Attribute:id"}),
        #"Expanded race" = Table.ExpandTableColumn(#"Removed Other Columns", "race", {"Attribute:id"}, {"race.Attribute:id"}),
        #"Expanded club" = Table.ExpandTableColumn(#"Expanded race", "club", {"Attribute:abbrevname", "Attribute:code", "Attribute:associationclass", "Attribute:website"}, {"club.Attribute:abbrevname", "club.Attribute:code", "club.Attribute:associationclass", "club.Attribute:website"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded club",{"club.Attribute:abbrevname", "club.Attribute:associationclass", "club.Attribute:website"})
    in
        #"Removed Columns"

    Query 4

    let
        Source = Xml.Tables(File.Contents("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"race"}),
        #"Expanded race" = Table.ExpandTableColumn(#"Removed Other Columns", "race", {"condition", "nomination", "Attribute:id", "Attribute:number", "Attribute:nomnumber", "Attribute:division", "Attribute:name", "Attribute:mediumname", "Attribute:shortname", "Attribute:stage", "Attribute:distance", "Attribute:minweight", "Attribute:raisedweight", "Attribute:class", "Attribute:age", "Attribute:grade", "Attribute:weightcondition", "Attribute:trophy", "Attribute:owner", "Attribute:trainer", "Attribute:jockey", "Attribute:strapper", "Attribute:totalprize", "Attribute:first", "Attribute:second", "Attribute:third", "Attribute:fourth", "Attribute:fifth", "Attribute:time", "Attribute:bonustype", "Attribute:nomsfee", "Attribute:acceptfee", "Attribute:trackcondition", "Attribute:timingmethod", "Attribute:fastesttime", "Attribute:sectionaltime", "Attribute:formavailable", "Attribute:racebookprize"}, {"race.condition", "race.nomination", "race.Attribute:id", "race.Attribute:number", "race.Attribute:nomnumber", "race.Attribute:division", "race.Attribute:name", "race.Attribute:mediumname", "race.Attribute:shortname", "race.Attribute:stage", "race.Attribute:distance", "race.Attribute:minweight", "race.Attribute:raisedweight", "race.Attribute:class", "race.Attribute:age", "race.Attribute:grade", "race.Attribute:weightcondition", "race.Attribute:trophy", "race.Attribute:owner", "race.Attribute:trainer", "race.Attribute:jockey", "race.Attribute:strapper", "race.Attribute:totalprize", "race.Attribute:first", "race.Attribute:second", "race.Attribute:third", "race.Attribute:fourth", "race.Attribute:fifth", "race.Attribute:time", "race.Attribute:bonustype", "race.Attribute:nomsfee", "race.Attribute:acceptfee", "race.Attribute:trackcondition", "race.Attribute:timingmethod", "race.Attribute:fastesttime", "race.Attribute:sectionaltime", "race.Attribute:formavailable", "race.Attribute:racebookprize"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Expanded race",{"race.nomination", "race.condition", "race.Attribute:id", "race.Attribute:number"}),
        #"Expanded race.condition" = Table.ExpandTableColumn(#"Removed Other Columns1", "race.condition", {"Attribute:line", "Element:Text"}, {"race.condition.Attribute:line", "race.condition.Element:Text"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded race.condition",{"race.condition.Attribute:line", "race.condition.Element:Text"}),
        #"Expanded race.nomination" = Table.ExpandTableColumn(#"Removed Columns", "race.nomination", {"Attribute:age", "Attribute:barrier", "Attribute:blinkers", "Attribute:bonusindicator", "Attribute:career", "Attribute:colours", "Attribute:deadtrack", "Attribute:decimalmargin", "Attribute:description", "Attribute:dob", "Attribute:fasttrack", "Attribute:finished", "Attribute:firstup", "Attribute:goodtrack", "Attribute:heavytrack", "Attribute:horse", "Attribute:id", "Attribute:idnumber", "Attribute:jockeyfirstname", "Attribute:jockeynumber", "Attribute:jockeysurname", "Attribute:maxdistancewin", "Attribute:mindistancewin", "Attribute:number", "Attribute:owners", "Attribute:penalty", "Attribute:pricestarting", "Attribute:rating", "Attribute:regnumber", "Attribute:rsbtrainername", "Attribute:saddlecloth", "Attribute:secondup", "Attribute:sectional1200", "Attribute:sectional200", "Attribute:sectional400", "Attribute:sectional600", "Attribute:sectional800", "Attribute:sex", "Attribute:slowtrack", "Attribute:thisdistance", "Attribute:thistrack", "Attribute:trainerfirstname", "Attribute:trainernumber", "Attribute:trainersurname", "Attribute:trainertrack", "Attribute:variedweight", "Attribute:weight", "Attribute:weightvariation"}, {"race.nomination.Attribute:age", "race.nomination.Attribute:barrier", "race.nomination.Attribute:blinkers", "race.nomination.Attribute:bonusindicator", "race.nomination.Attribute:career", "race.nomination.Attribute:colours", "race.nomination.Attribute:deadtrack", "race.nomination.Attribute:decimalmargin", "race.nomination.Attribute:description", "race.nomination.Attribute:dob", "race.nomination.Attribute:fasttrack", "race.nomination.Attribute:finished", "race.nomination.Attribute:firstup", "race.nomination.Attribute:goodtrack", "race.nomination.Attribute:heavytrack", "race.nomination.Attribute:horse", "race.nomination.Attribute:id", "race.nomination.Attribute:idnumber", "race.nomination.Attribute:jockeyfirstname", "race.nomination.Attribute:jockeynumber", "race.nomination.Attribute:jockeysurname", "race.nomination.Attribute:maxdistancewin", "race.nomination.Attribute:mindistancewin", "race.nomination.Attribute:number", "race.nomination.Attribute:owners", "race.nomination.Attribute:penalty", "race.nomination.Attribute:pricestarting", "race.nomination.Attribute:rating", "race.nomination.Attribute:regnumber", "race.nomination.Attribute:rsbtrainername", "race.nomination.Attribute:saddlecloth", "race.nomination.Attribute:secondup", "race.nomination.Attribute:sectional1200", "race.nomination.Attribute:sectional200", "race.nomination.Attribute:sectional400", "race.nomination.Attribute:sectional600", "race.nomination.Attribute:sectional800", "race.nomination.Attribute:sex", "race.nomination.Attribute:slowtrack", "race.nomination.Attribute:thisdistance", "race.nomination.Attribute:thistrack", "race.nomination.Attribute:trainerfirstname", "race.nomination.Attribute:trainernumber", "race.nomination.Attribute:trainersurname", "race.nomination.Attribute:trainertrack", "race.nomination.Attribute:variedweight", "race.nomination.Attribute:weight", "race.nomination.Attribute:weightvariation"}),
        #"Removed Other Columns2" = Table.SelectColumns(#"Expanded race.nomination",{"race.Attribute:number", "race.Attribute:id", "race.nomination.Attribute:weight", "race.nomination.Attribute:trainernumber", "race.nomination.Attribute:thistrack", "race.nomination.Attribute:thisdistance", "race.nomination.Attribute:secondup", "race.nomination.Attribute:saddlecloth", "race.nomination.Attribute:rsbtrainername", "race.nomination.Attribute:pricestarting", "race.nomination.Attribute:number", "race.nomination.Attribute:jockeynumber", "race.nomination.Attribute:horse", "race.nomination.Attribute:goodtrack", "race.nomination.Attribute:firstup", "race.nomination.Attribute:finished", "race.nomination.Attribute:dob", "race.nomination.Attribute:description", "race.nomination.Attribute:decimalmargin", "race.nomination.Attribute:career", "race.nomination.Attribute:barrier", "race.nomination.Attribute:age"})
    in
        #"Removed Other Columns2"


    Taking my C# further

    Tuesday, March 22, 2016 12:16 PM

Answers

  • You'd do something like the following. Let's take your first query as an example. First, you'd turn it into a function:

    (fileContent) =>
    let
        Source = Xml.Tables(fileContent),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Attribute:id", "Attribute:venue", "Attribute:date", "Attribute:trackcondition"})
    in
        #"Removed Other Columns"

    Name this query GetQuery1Table.

    Then you'd use "From Folder" to get a list of just the files that match the format expected by this function, add a custom column, and in the custom column expression, you'd do something like the following:

    = GetQuery1Table([Content])

    Finally, you'd add a custom step (by clicking the little "fx" button in the formula bar), and call:

    = Table.Combine(PreviousStepName[CustomColumnYouAdded])

    Where "PreviousStepName" is the name of the previous step, and "CustomColumnYouAdded" is the name of the custom column you added above.

    This would process each file using the logic from your original Query1 logic, then append them together into a single table. The same approach could be used for the other three kinds of files, resulting in four separate tables containing each of the four different kinds of file data.

    Ehren

    • Marked as answer by SaythJ Tuesday, April 26, 2016 8:14 AM
    Monday, April 25, 2016 10:41 PM
    Owner

All replies

  • Hi SaythJ. For each xml input file in the folder, are you wanting to end up with four distinct queries/tables as output? Or are they merged into one result table?

    Ehren

    Thursday, March 24, 2016 8:28 PM
    Owner
  • Hi Ehren

    I am amenable to either solution, in its current form as 4 separate tables I am creating relationships between the tables in the Data Model. Initially I tried to merge the 4 queries back to one however couldn't achieve it.

    However with the size of the data from the subsequent XML imports I will be exporting it to a database to store and read back, likely Access to keep the solution small.

    Thanks

    Sayth


    Taking my C# further


    • Edited by SaythJ Thursday, March 24, 2016 9:49 PM
    Thursday, March 24, 2016 9:34 PM
  • Hi Sayth. Currently there's no way for a single query to output multiple results, so producing four separate tables for each xml file in a folder isn't possible.

    However, if the four different results can be combined somehow (either through Append Queries or Merge Queries), then you should be able to run this operation over a folder of xml files and produce a single, combined result.

    Ehren

    Monday, March 28, 2016 10:31 PM
    Owner
  • Ehren there of course nothing stopping me from making each query it's own folder query and leaving the relationships to the data model.

    However is there a way to "chain" the folder queries together as a macro so they occur one after the other?


    Taking my C# further

    Monday, April 4, 2016 1:54 AM
  • In Excel 2016, you can script the creation and refreshing of Power Query queries via Excel macros. More info here: http://blog.crossjoin.co.uk/2015/06/10/power-queryexcel-2016-vba-examples/

    Ehren

    Monday, April 4, 2016 5:40 PM
    Owner
  • From that Crossjoin article it refers to a github repository https://github.com/tycho01/pquery of Tycho01.

    I raised an issue and Tycho01 kindly replied, proposed a solution with multiple tables as

    To parameterize your queries so you could use them on multiple files, make them into functions. i.e. from

    let Source = Xml.Tables(File.Contents("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")),
     to:
        (FileName) => let Source = Xml.Tables(File.Contents(FileName)),
    

    This yields a reusable function that you can then reuse with:

     functionName("C:\Users\Sayth\OneDrive\Racing\20160305RAND0.xml")

    And finally then chain those function together.

    (FileName) => [FirstResult=functionName(FileName), ...]
    
    The solution is in this github issue https://github.com/tycho01/pquery/issues/7


    Taking my C# further

    Friday, April 8, 2016 4:06 AM
  • I am stuck in assigning the global query that will invoke the 4 others from a folder, so far I have.

    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

    Or

    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

    Both of which fail syntax parsing



    • Edited by SaythJ Friday, April 15, 2016 11:23 AM
    Friday, April 15, 2016 11:16 AM
  • Hi SaythJ,

    What are you wanting the output to be? Four separate tables in the data model?

    Ehren

    Tuesday, April 19, 2016 7:53 PM
    Owner
  • Yes four separate tables, one for each query.

    Taking my C# further

    Wednesday, April 20, 2016 9:09 AM
  • Ok. There's currently no way to have a single query (or single function) return something that generates four separate data model tables. You'll need to have one query for each of the data model tables (although the queries can share logic or call the same function and pass different arguments).

    Ehren

    Wednesday, April 20, 2016 4:52 PM
    Owner
  • Thanks I have asked over at https://github.com/tycho01/pquery/issues/7 just to confirm.



    Taking my C# further

    Saturday, April 23, 2016 12:15 AM
  • Ok. There's currently no way to have a single query (or single function) return something that generates four separate data model tables. You'll need to have one query for each of the data model tables (although the queries can share logic or call the same function and pass different arguments).

    Ehren

    Ok so if I did one query on the folder and return that one query as a sheet, is that possible? If yes how?

    Taking my C# further

    Saturday, April 23, 2016 11:39 AM
  • You'd do something like the following. Let's take your first query as an example. First, you'd turn it into a function:

    (fileContent) =>
    let
        Source = Xml.Tables(fileContent),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Attribute:id", "Attribute:venue", "Attribute:date", "Attribute:trackcondition"})
    in
        #"Removed Other Columns"

    Name this query GetQuery1Table.

    Then you'd use "From Folder" to get a list of just the files that match the format expected by this function, add a custom column, and in the custom column expression, you'd do something like the following:

    = GetQuery1Table([Content])

    Finally, you'd add a custom step (by clicking the little "fx" button in the formula bar), and call:

    = Table.Combine(PreviousStepName[CustomColumnYouAdded])

    Where "PreviousStepName" is the name of the previous step, and "CustomColumnYouAdded" is the name of the custom column you added above.

    This would process each file using the logic from your original Query1 logic, then append them together into a single table. The same approach could be used for the other three kinds of files, resulting in four separate tables containing each of the four different kinds of file data.

    Ehren

    • Marked as answer by SaythJ Tuesday, April 26, 2016 8:14 AM
    Monday, April 25, 2016 10:41 PM
    Owner
  • I get to the last  step using the names as in the post I called the custom column Venue however cannot get table.combine to work.

    = Table.Combine(GetQuery1Table([Venue]))

    or this also fails.

    = Table.Combine(GetQuery1Table([Content])[Venue])

    They will return an error like.

    Expression.Error: We cannot apply field access to the type Function.
    Details:
        Value=Function
        Key=Venue



    • Edited by SaythJ Sunday, May 29, 2016 7:23 AM
    Sunday, May 29, 2016 7:09 AM
  • Tried it in a different format akin to these docs however still not working.

    = Table.Combine({GetQuery1Table({[Content],[Venue]})})

    https://msdn.microsoft.com/en-us/library/mt260748.aspx?f=255&MSPPError=-2147217396

    Any ideas?

    As an aside I also created a suggestion on uservoice to formalise and fix the folder import so that it has a consistent import so others don't need to struggle with it like I have, which is a shame because it would vastly increase the power of powerquery if fixed.

    PowerBI Uservoice



    • Edited by SaythJ Monday, May 30, 2016 11:38 AM
    Monday, May 30, 2016 11:37 AM
  • Can you attach a screenshot of the query editor (including the formula bar) prior to the point where you're trying to do the Table.Combine?

    Ehren

    Tuesday, May 31, 2016 5:04 PM
    Owner

  • Taking my C# further

    Friday, June 3, 2016 8:33 AM
  • Ok, following the example I gave above, the custom step should be:

    = Table.Combine(#"Added Custom"[Venue])

    Let me know if that works.

    Ehren

    Friday, June 3, 2016 4:18 PM
    Owner
  • Ok that works, can I ask why how would I do it again for further queries? is it always going to be #'Added Custom + the name of the content?

    Taking my C# further

    Saturday, June 4, 2016 4:13 AM
  • Glad it worked!

    To do this again for further queries, just use the PreviousStepName followed by [CustomColumnName]. The first part (previous step name) will only be #"Added Custom" if that's the name of the preivous step.

    To get this part (without the Table.Combine) generated for you, you can right click on the custom column you added (Venue in your current example), and select Drill Down. Then modify the formula to add the Table.Combine call.

    Ehren


    Monday, June 6, 2016 5:49 PM
    Owner
  • Hi Ehren

    This method is seeming to create another error now which I do not understand (other than don't work with XML :-)).

    But if I back it up one step and use the ToList function created from the transformation tools I found using drill down as per your previous advice I can see all the data. I notice at the very bottom one bad row is that the error or unrelated?

    PS I had to also remove excel 32-bit and reinstall as 64-bit as query was slowing.


    Taking my C# further

    Thursday, June 9, 2016 12:08 PM
  • Yes, that error in row 44 is preventing us from parsing the xml any further, and when you try to do the Table.Combine, the error bubbles up to the top level.

    It appears there's something wrong with the formatting of the xml document. If that turns out not to be the case, let us know, as that means there's a bug in our xml parsing.

    Ehren

    Thursday, June 9, 2016 5:45 PM
    Owner
  • I located the file and it appears to be a file error something was wrong with encoding.

    Taking my C# further

    Friday, June 10, 2016 12:13 PM