cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
paultimms New Member
New Member

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

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:

Capture.JPG

 

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?

1 REPLY 1
Super User IV
Super User IV

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

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors