Forum Discussion

Zefi's avatar
Zefi
New Member
7 years ago
Solved

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 ...
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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>
Zefi's avatar
Zefi
New Member
7 years ago

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's avatar
Nolock
Resident Rockstar
7 years ago

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>