Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Import this xml into PBI Desktop:
<?xml version="1.0" encoding="utf-8"?> <table> <row> <AllValues>111</AllValues> <AllEmpty></AllEmpty> <Mixed1>abcd</Mixed1> <Mixed2>abcd</Mixed2> <Mixed3></Mixed3> </row> <row> <AllValues>222</AllValues> <AllEmpty></AllEmpty> <Mixed1></Mixed1> </row> <row> <AllValues>333</AllValues> <AllEmpty></AllEmpty> <Mixed1></Mixed1> </row> </table>
In Query editor the result is:
In the UI you cannot expand the columns AllEmpty and Mixed3, since PQ cannot find any columns in the empty tables.
Note the difference between Mixed1 and Mixed2, in row 1 they both have the same value 'abcd'. However, in Mixed1 this value is wrapped in a table.
Solution: Power Query should never wrap simple xml-elements in a table. An empty xml-element should always transform to a null, and so should a missing element.
Workaround: use a function like Chris Webb describes here (which fails for AllEmpty above, btw).
Solving this issue would make processing xml files in PBI and Excel a lot easier!!
Thx.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.