Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
v-haibl-msft
Employee

@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

Vicky_Song
Impactful Individual
Status changed to: Accepted
 
v-haibl-msft
Employee

@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

Vicky_Song
Impactful Individual
Status changed to: Delivered
 
fransbus
Regular Visitor

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

darby413
New Member

@v-haibl-msft Hello, is there an update on this issue? I see you last commemted about Microsoft's backlog to fix this in 2017. I am experiencing this same issue now.