cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Zefi Frequent Visitor
Frequent Visitor

Nested table issue with TransformColumns

Hello Experts,

 

I would like to transform following tables into the same cell in this format:

“Prop1=wood,abc;Prop2=red,red2;Prop3=ok,ok2;Prop4=400,ooo1,ooo2”

 

(I was somehow able to transfor from prop1-3, but get stuck at prop4)

Can you please help me with the proper syntax?

 

 

Input xml sample:

 

<?xml version="1.0" encoding="UTF-8"?>
<documents>
<document>
<property>
<name>prop1</name>
<subprop>
<value1>wood</value1>
<value2>abc</value2>
</subprop>
</property>
<property>
<name>prop2</name>
<subprop>
<value1>red</value1>
<value2>red2</value2>
</subprop>
</property>
<property>
<name>prop3</name>
<subprop>
<value1>ok</value1>
<value2>ok2</value2>
</subprop>
</property>
<property>
<name>prop4</name>
<subprop>
<value1>400</value1>
<value2>
<value3>ooo1</value3>
<value4>ooo2</value4>
</value2>
</subprop>
</property>
</document>
</documents>

 

 

 

Thank you very much,

Best Regards,

Zefi

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Nested table issue with TransformColumns

Hi @Zefi,

try following PowerQuery query:

let
    Source = Xml.Tables(File.Contents("PATHTOXMLFILE\test.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{0}[Table],
    #"Expanded subprop" = Table.ExpandTableColumn(Table1, "subprop", {"value1", "value2", "value3", "value4"})
in
    #"Expanded subprop"

You have to tell PowerBI that it shouldn't just expand the columns value1 and value2, but also value3, value4 and so on if there are more columns available.

 

And a screenshot of the result:

Capture.PNG

Data used for testing:

<?xml version="1.0" encoding="UTF-8"?>
<documents>
<document>
<property>
<name>prop1</name>
<subprop>
<value1>wood</value1>
<value2>abc</value2>
</subprop>
</property>
<property>
<name>prop2</name>
<subprop>
<value1>red</value1>
<value2>red2</value2>
</subprop>
</property>
<property>
<name>prop3</name>
<subprop>
<value1>ok</value1>
<value2>ok2</value2>
</subprop>
</property>
<property>
<name>prop4</name>
<subprop>
<value1>400</value1>
<value2>ooo0</value2>
<value3>ooo1</value3>
<value4>ooo2</value4>
</subprop>
</property>
</document>
</documents>
3 REPLIES 3
Super User
Super User

Re: Nested table issue with TransformColumns

Hi @Zefi,

are you sure, that this property with value2 is correct? The prop1, prop2, and prop3 have just one text value for value2, but in prop4 it is an object. And that is also the problem why the xml file isn't parsed.

<property>
<name>prop4</name>
<subprop>
<value1>400</value1>
<value2>
<value3>ooo1</value3>
<value4>ooo2</value4>
</value2>
</subprop>
</property>
Zefi Frequent Visitor
Frequent Visitor

Re: Nested table issue with TransformColumns

Hi @Nolock ,

 

maybe I was wrong with the example.

 

this is how it looks like:

 

power_q_screenshot

 

and expected transformation: name1=value1;name2=value2;name3=value3;......

 

Thank you for your help,

Best regards,

Zefi

 

 

Super User
Super User

Re: Nested table issue with TransformColumns

Hi @Zefi,

try following PowerQuery query:

let
    Source = Xml.Tables(File.Contents("PATHTOXMLFILE\test.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{0}[Table],
    #"Expanded subprop" = Table.ExpandTableColumn(Table1, "subprop", {"value1", "value2", "value3", "value4"})
in
    #"Expanded subprop"

You have to tell PowerBI that it shouldn't just expand the columns value1 and value2, but also value3, value4 and so on if there are more columns available.

 

And a screenshot of the result:

Capture.PNG

Data used for testing:

<?xml version="1.0" encoding="UTF-8"?>
<documents>
<document>
<property>
<name>prop1</name>
<subprop>
<value1>wood</value1>
<value2>abc</value2>
</subprop>
</property>
<property>
<name>prop2</name>
<subprop>
<value1>red</value1>
<value2>red2</value2>
</subprop>
</property>
<property>
<name>prop3</name>
<subprop>
<value1>ok</value1>
<value2>ok2</value2>
</subprop>
</property>
<property>
<name>prop4</name>
<subprop>
<value1>400</value1>
<value2>ooo0</value2>
<value3>ooo1</value3>
<value4>ooo2</value4>
</subprop>
</property>
</document>
</documents>

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 241 members 2,975 guests
Please welcome our newest community members: