XML is a real blast from the past but every now and then one has to work with legacy formats.
I’ve used XML files with Power BI Desktop many times with no problems. Obviously it’s not the greatest format performance wise and there are some flaws while converting XML files in Power BI Desktop. But still it does what it’s supposed to do and you can work with it. Atleast when we’re talking about small amounts of data.
Some time ago I came across a case which required XML handling in Power BI Data Flow. Surprisingly XML handling in Data Flow wasn’t working as I expected based on Power BI Desktop experience. I spent couple of hours with this problem and finally found fairly simple solution.
So let’s see. I have binary data from XML files in Data Flow and everything is fine so far.
Next step is to convert binary data to table. Just like in Power BI Desktop conversion creates function which is used in conversion. Everything seems to be okay so far.
Now all I have to do is drill into data fields. But here’s the problem. I drill to data level and as you can see in the picture table conversion haven’t worked as expected. I have few columns and lots of rows containing column names.
I tested exactly same handling process with same data in Power BI Desktop and ended with successful conversion.
I copied working query and related functions from Power BI Desktop to Data Flow. But no, I ended up again with failed conversion and falsely structured table.
Finally I found the solution which was super simply. The key is to use Xml.Tables function.
Let’s rewind to phase where we have binary data fields for each XML file in Data Flow. At this point I have no related functions or other tables, just our original query.
At this point M code looks like this.
Now all I need to do is add new custom column and use Xml.tables function to convert binary data to table.
And here we go. No additional functions created. Now I can remove original binary data field and drill down on new Conversion column.
After drill down data looks a lot better than before.
Simple as that.