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

[Hamster0406]

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 that don't exist in other XML files. I can't change the source system which generates this.

Company1

Company2

If you look at XML samples attached, 1 XML has TAN and TANREG tags missing. 

I have tried to use try catch with Table.ExpandTableColumn, but it is becoming cumbersome to maintain for so many of columns. 

Is there any other way we can extract the data but as PQ to insert null automatically?

EDIT:

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

[Anonymous]

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]

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]

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 · Accepted solution]

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.