Nested table issue with TransformColumns

[Zefi]

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

[Nolock]

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]

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

[Nolock · Accepted solution]

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:

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>