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 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

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>

3 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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'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>