Forum Discussion
Problem when expanding XML table fields to rows instead of columns
Hi!
I'm processing an folder with XML files (SEPA Direct Debit Rejections) and have reached to this point:
When expanding the table, instead of creating new COLUMNS, each of tipe 'Table', it creates new ROWS:
The command used is:
= Table.ExpandTableColumn(#"Personalizada agregada", "Transformar archivo", Table.ColumnNames(#"Transformar archivo"(#"Archivo de ejemplo")))
Why does this happens? Is there a way to force to expand in columns?
If not? Could I move the rows to columns? I have trie to PIVOT, but it says can't pivot TABLE columns. Can I merge again the rows somehow?
Thanks!
At the end, what I did was REFER the table three times (three new tables), and, on each, filter by column (one for GrpHdr, other for OrgnlGrpInfAndSts and a last one for OrgnlPmtInfAndSts. Then I expand them separately, and JOIN them somehow later in the model.
It has been the only solution I have found. 😞
3 Replies
Have you tried expanding on that column?
It just makes the problem bigger, as some of the new fileds are part o GrpHdr, some of OrgnlGrpInfAndSts... and so on...
Thanks for your idea, anyway 😉
At the end, what I did was REFER the table three times (three new tables), and, on each, filter by column (one for GrpHdr, other for OrgnlGrpInfAndSts and a last one for OrgnlPmtInfAndSts. Then I expand them separately, and JOIN them somehow later in the model.
It has been the only solution I have found. 😞
