none
Xml.Tables returning table instead of null value RRS feed

  • Question

  • Quick description on what is being done. Maybe I need to do a different approach.
    So basically in SQL some dynamic tables are stored as a column that is in xml.
    When getting this data out with power query i use a formula on the xml column to get the attributes and their values:

    XmlTrans = Table.TransformColumns(#"Filtered Rows", {"Data", each Xml.Tables(_)})
    

    The result is a table that is expandable with the result i require with one exception.
    Whenever the value was null the result will not be a value but a table (table in a table).

     The data part in the from the sql was:

    How can i get null values there instead ?

    Tuesday, May 9, 2017 10:00 AM

Answers

  • I'm having trouble reproducing what you pasted above (a column of nested tables, but one that can't be expanded and just contains another nested table inside of it). Can you create a small xml sample that demonstrates the issue you're seeing?

    If I create a simple xml file and put an attribute on one of the fields, I do see a nested table for that column. But each column of the nested table corresponds to an element of the xml (e.g. the attribute of the field, or the text value inside the field), and I'm able to expand them.

    Ehren


    Wednesday, May 17, 2017 7:14 PM
    Owner

All replies

  • You could replace the empty tables with nulls via a column transformation step, like this:

    = Table.TransformColumns(PreviousStep, {{"AddressPart2", each if Table.RowCount(_) = 0 then null else _}})

    Ehren

    Monday, May 15, 2017 7:48 PM
    Owner
  • Hi Ehren, it's not even an empty table, it looks like below. Basically another table in a table :)

    There isint any cleaner way to do it ?

    Wednesday, May 17, 2017 7:21 AM
  • I'm having trouble reproducing what you pasted above (a column of nested tables, but one that can't be expanded and just contains another nested table inside of it). Can you create a small xml sample that demonstrates the issue you're seeing?

    If I create a simple xml file and put an attribute on one of the fields, I do see a nested table for that column. But each column of the nested table corresponds to an element of the xml (e.g. the attribute of the field, or the text value inside the field), and I'm able to expand them.

    Ehren


    Wednesday, May 17, 2017 7:14 PM
    Owner
  • Agree with Ehren, that it looks strange.

    A different approach would be this:

    = Table.TransformColumns(PreviousStep, {{"AddressPart2", each try Text.From(_) otherwise null}})


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    • Proposed as answer by JanasBanana Wednesday, October 10, 2018 2:34 AM
    Friday, June 23, 2017 5:36 PM
    Moderator
  • This solution worked for me.
    Wednesday, October 10, 2018 2:33 AM