Power Query doesn't handle nil attribute properly (Table Type)

[iimitz]

As per the XML Schema specifications, the xsi:nil attribute is utilized to represent that an element should be accepted when it has no value set (https://www.w3.org/TR/xmlschema-1/#xsi_nil). In all applications I've used in the past, this would be attributed with a NULL value if the consuming application accepts it. Power Query has different results depending on the tag structure used to represent this.

Regular XML:
Standard data, represented as expected; "firstName" column with "John" value, "lastName" column with "Doe" value.

<Example xmlns:i="http://www.w3.org/2001/XmlSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Example">
    <firstName>John</firstName>
    <lastName>Doe</lastName>
</Example>

Empty element with start/end tags:

Shows lastName column value as a "Table" value that must be expanded and have the user select "Element:Text" to see null as a value.

<Example xmlns:i="http://www.w3.org/2001/XmlSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Example">
    <firstName>John</firstName>
    <lastName></lastName>
</Example>

Empty element with self-closing tag:

Shows lastName as a null value (as desired).

<Example xmlns:i="http://www.w3.org/2001/XmlSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Example">
    <firstName>John</firstName>
    <lastName/>
</Example>

Empty element with self-closing tag and nil attribute:

Shows lastName column value as "Table" value that must be expanded and have user select "Element:Text" to see null as value.

<Example xmlns:i="http://www.w3.org/2001/XmlSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Example">
    <firstName>John</firstName>
    <lastName i:nil="true"/>
</Example>

Missing element tag:

Shows lastName of "John" as a null value (as desired).

<Examples xmlns:i="http://www.w3.org/2001/XmlSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Examples">
    <Example>
        <firstName>John</firstName>
    </Example>
    <Example>
        <firstName>Jane</firstName>
        <lastName>Doe</lastName>
    </Example>
</Examples>

Can someone help me understand why Power Query needs input from me to determine that the self closed element is equivelant to the self closed element with the nil attribute? Older versions of Excel were able to properly deduce with and present the data with an empty field. 

I'm using .NET Core Web API to provide XML data to my end users for their reporting purposes. This is my first time utilizing newer versions of Excel and Power BI that leverage the Power Query services. It does not treat the data the same way, and I can't have my end users having to manually go through every column and adjust the data just so they can identify "Element:Text" as the element they want for that column. All standard XML Output formatters inject the nil="true" attribute as per the spec. I can omit the element altogether if it's null or default, but Microsoft strongly advises against that approach.

[ronrsnfld · Accepted solution]

Apparently this is a limitation of the current version of Power Query.

One way to handle this is to include code that replaces the Table value with null.

eg:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    xmlTable = Xml.Tables(#"Changed Type"[Column1]{0}),
//Replace "Tables" with null
    #"Null Elements" = Table.ReplaceValue(
        xmlTable,
        null,
        null,
        (x,y,z) as nullable text=> if Value.Is(x, type table) then null else x,
        Table.ColumnNames(xmlTable)
    )
in
    #"Null Elements"