cancel
Showing results for 
Search instead for 
Did you mean: 

Power Query should not wrap simple xml elements in a table

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:

 

Knipsel.PNG

 

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.

Status: Delivered
Comments
Microsoft

@fransbus

 

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 31465818

I’ll post here once I get any update about it.

 

Best Regards,

Herbert

Impactful Individual
Status changed to: Accepted
 
Microsoft

@fransbus

 

I’ve got response from Power BI Team. They are aware of this deficiency. It's being tracked on their backlog and they have no specific plans to address it now. Solving this issue would require to load the entire table into memory at once. This would potentially break existing scenarios which rely on us streaming the document for performance.

 

Best Regards,

Herbert

Impactful Individual
Status changed to: Delivered
 
New Member

Hi Herbert,

 

Thx for your interference.

Basicaly I can understand the answer. However, it is difficult to understand and to explain why there is a difference in processing "normal" empty elements like I used above, elements with a self closing tag like <mixed />, and completely missing elements.

Replace all empty elements in the xml above by self closing tags or simply remove them, then there are no issues...

 

 

Regards,

- Frans