The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.