Forum Discussion

Hamster0406's avatar
Hamster0406
Frequent Visitor
2 years ago
Solved

Ignoring error in Table.ExpandTableColumn or auto-replace with null

I am loading 100s of XML files from a folder and Power Query has created a function from the first file that I had loaded. The problem is not all the XML files have similar structure. Some have nodes...
Need Help
  • Hamster0406's avatar
    Hamster0406
    2 years ago

    Thanks! This could work, but I would rather go with the simple method mentioned by the other poster on Stackoverflow as it would be easy to understand and maintain by anyone other than me.

     

    Thanks again for taking the time to help me.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Hamster0406 

 

Do not pick the first file as Sample file. Instead, pick a specified file as Sample file if you are sure that that file has all possible tags. If you are not sure which file will have all possible tags, prepare an additional template file that has all tags and pick this file as Sample file. When expanding columns for all files, it will try to find all tags according to the template file structure. As a result, for the missing tags, it will fill with null by default.

 

You see in below image, the TAN and TANREG columns don't have errors as I selected "Company 2 info.xml" file as Sample file. 

 

The Key is to make sure that the file picked as Sample file has all tags. If you use a template file that has unwanted useless data, you can remove that file row through filtering by Source.Name column in the combined query.

 

Hope this will be helpful. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Hamster0406's avatar
    Hamster0406
    Frequent Visitor
    2 years ago

    This won't work as I gave only a sample of fields. There are over 100 files and each has slightly different tags that may or may not be available in other files. I will also have to load the file in individually as there are other complex transformations to be done. I can send the file across to a single table only after all of them are processed individually.

    I have also created a sample file which has a few bits of code that I am using to load the data.

    Link to sample Excel with Power Query

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Hi Hamster0406, try this - it works with sample, but maybe it won't with real data:

       

      Result

       

      You can delete whole Transform File Group and replace code of your Tally ETL Issue query with this code:

       

      let
    Source = Folder.Files("C:\Users\Yashw\OneDrive\Email attachments\Tally ETL Issue\"),
    FilteredRows = Table.SelectRows(Source, each ([Extension] = ".xml")),
    BinaryToTable = Table.TransformColumns(FilteredRows, {{"Content", Xml.Tables}}),
    Ad_Transform = Table.AddColumn(BinaryToTable, "Transform", each 
        [ t1 = [Content]{1}[Table],
          t2 = t1{1}[Table],
          t3 = t2{0}[Table],
          t4 = t3{0}[Table],
          ExpandedColumns = List.Accumulate(Table.ColumnNames(t4), t4,
                (s,c)=> try Table.ExpandTableColumn(s, c, Table.ColumnNames(Table.Column(s, c){0}), List.Transform(Table.ColumnNames(Table.Column(s, c){0}), (x)=> c & "|" & x)) otherwise s),
          Ad_SourceName = Table.AddColumn(ExpandedColumns, "SourceName", (x)=> [Name]),
          Reordered = Table.ReorderColumns(Ad_SourceName, {"SourceName"} & Table.ColumnNames(ExpandedColumns))
        ][Reordered], type table),
    CombinedAllFiles = Table.Combine(Ad_Transform[Transform])
in
    CombinedAllFiles

       

      • Hamster0406's avatar
        Hamster0406
        Frequent Visitor
        2 years ago

        Thanks! This could work, but I would rather go with the simple method mentioned by the other poster on Stackoverflow as it would be easy to understand and maintain by anyone other than me.

         

        Thanks again for taking the time to help me.