Forum Discussion

iimitz's avatar
iimitz
New Member
2 years ago
Solved

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

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 a...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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"
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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"