Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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>
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>
Hi @Nolock ,
maybe I was wrong with the example.
this is how it looks like:
and expected transformation: name1=value1;name2=value2;name3=value3;......
Thank you for your help,
Best regards,
Zefi
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>
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.