Forum Discussion
Ignoring error in Table.ExpandTableColumn or auto-replace with null
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.
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.
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
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.