Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paultimms
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
Mariusz
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors