Why does "Table" appear instead of the actual value?

[paultimms]

I'm importing some XML into Excel. Using the legacy import, it works fine, but I'm having problems with Power Query Editor. A short example of the XML is as follows:

<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<data>
 <IVVc>
  <SerNr>2178027</SerNr>
  <InvDate>2018-01-02</InvDate>
  <CustCode>4622</CustCode>
  <Addr0>Joe Bloggs Ltd</Addr0>
  <ClientContact>Fred</ClientContact>
  <CustCat>PURCH</CustCat>
  <rows>
   <row rownumber="0">
    <ArtCode>ASS003A</ArtCode>
    <Sum>74.00</Sum>
   </row>
   <row rownumber="1">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
   <row rownumber="2">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
   <row rownumber="3">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
  </rows>
 </IVVc>
 <IVVc>
  <SerNr>2178028</SerNr>
  <InvDate>2018-01-02</InvDate>
  <CustCode>4622</CustCode>
  <Addr0>Joe Bloggs Ltd</Addr0>
  <ClientContact></ClientContact>
  <CustCat>PURCH</CustCat>
  <rows>
   <row rownumber="0">
    <ArtCode>ASS003A</ArtCode>
    <Sum>74.00</Sum>
   </row>
   <row rownumber="1">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
   <row rownumber="2">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
   <row rownumber="3">
    <ArtCode></ArtCode>
    <Sum></Sum>
   </row>
  </rows>
 </IVVc>
 <IVVc>
  <SerNr>2178046</SerNr>
  <InvDate>2018-01-02</InvDate>
  <CustCode>5324</CustCode>
  <Addr0>Mrs Bloggs</Addr0>
  <ClientContact></ClientContact>
  <CustCat>MISCC</CustCat>
  <rows>
   <row rownumber="0">
    <ArtCode>SAMPLE</ArtCode>
    <Sum>200.00</Sum>
   </row>
   <row rownumber="1">
    <ArtCode>SAMPLE</ArtCode>
    <Sum>100.00</Sum>
   </row>
  </rows>
 </IVVc>
</data>

When I expand the rows, then row, I get the following:

If I try to expand ClientContact, it says "No columns were found" despite there being data in the first invoice - it should say "Fred".

In the ArtCode and Sum columns, the values only seem to be displayed if every row on the invoice has a value, i.e. not null. Clicking on the Table cell in the ArtCode column on row 1, shows in the window at the bottom Element:Text with ASS003A beneath.

How do I get the data to show if there are null values on some rows?

[Mariusz]

Hi @paultimms 

My assumption is that when you expand your columns and they are mixed data types ( text and table ) then it will not be able to expand them further, however, you can expand this column using M.

Try something like below.

let
    Source = Xml.Tables(File.Contents("*** xml file path ****")),
    Table0 = Source{0}[Table],
    #"Expanded ClientContact" = Table.ExpandTableColumn(Table0, "ClientContact", {"Element:Text"}, {"Element:Text"}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Expanded ClientContact", "rows", {"row"}, {"row"}),
    #"Expanded row" = Table.ExpandTableColumn(#"Expanded rows", "row", {"ArtCode", "Sum", "Attribute:rownumber"}, {"ArtCode", "Sum", "Attribute:rownumber"}),
    #"Added Custom" = Table.AddColumn(#"Expanded row", "Custom", each 
        let 
            column = [ArtCode],
            checkType = Value.Is( column, type table ),
            getValue = try ( column )[#"Element:Text"] otherwise { null },
            return = if checkType then getValue else { column }
        in 
            return ),
     #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each let 
            column = [Sum],
            checkType = Value.Is( column, type table ),
            getValue = try ( column )[#"Element:Text"] otherwise { null },
            return = if checkType then getValue else { column }
        in 
            return),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Expanded Custom2" = Table.ExpandListColumn(#"Expanded Custom", "Custom2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom2",{"ArtCode", "Sum"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom2", type number}, {"Custom", type text}})
in
    #"Changed Type"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.