M: Why does the last line still returns a table instead of expanded text

[Satch]

Sometimes my xml file returns table in the addressBillingZipcode, sometimes zip as text

That depends on the source where I get it from.

So to use M to expand as ext if type is table, else just return the zip as text

let
Source = Xml.Tables(File.Contents("C:\Data\customer.xml")),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"addressShippingCompany", type text}, {"birthDate", type text}, {"createdAt", type datetimezone}, {"doNotifyConfirmed", Int64.Type}, {"doNotifyPassword", Int64.Type}, {"doNotifyRegistered", Int64.Type}, {"firstname", type text}, {"gender", type text}, {"id", Int64.Type}, {"isCompany", Int64.Type}, {"isConfirmed", Int64.Type}, {"lastOnlineAt", type datetimezone}, {"lastname", type text}, {"referralId", Int64.Type}, {"remoteIp", type number}, {"type", type text}, {"updatedAt", type datetimezone}, {"userAgent", type text}}),
#"Expanded addressBillingCountry" = Table.ExpandTableColumn(#"Changed Type", "addressBillingCountry", {"code", "title"}, {"addressBillingCountry.code", "addressBillingCountry.title"}),
#"Added Custom" = Table.AddColumn(#"Expanded addressBillingCountry", "Zip", each if Value.Is([addressBillingZipcode], type table) then Table.ExpandTableColumn(#"Expanded addressBillingCountry", "addressBillingZipcode", {"Element:Text"}, {"Address.Element:Text"}) else [addressBillingZipcode])
in
#"Added Custom"

However above code still returns a column Zip with table in it?

Somebody knows why?

[MarcelBeug · Accepted solution]

Edit: this reply was a concurrent update with Imke's reply.

It looks like we have different interpretations.

Yes, if you add a column, it will be 1 column. If the formula returns a table, then it will be a nested table.

You may notice that each nested table in "Zip", is the complete table from the previous step, with column "addressBillingZipcode" expanded.

Instead of adding a column you can test the first field if it is a table; if so then you expand the table column, if not you return the table from the previous step. Something like the code below (I hope it's OK as I can't test without data; anyhow it has no syntax errors).

let
    Source = Xml.Tables(File.Contents("C:\Data\customer.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"addressShippingCompany", type text}, {"birthDate", type text}, {"createdAt", type datetimezone}, {"doNotifyConfirmed", Int64.Type}, {"doNotifyPassword", Int64.Type}, {"doNotifyRegistered", Int64.Type}, {"firstname", type text}, {"gender", type text}, {"id", Int64.Type}, {"isCompany", Int64.Type}, {"isConfirmed", Int64.Type}, {"lastOnlineAt", type datetimezone}, {"lastname", type text}, {"referralId", Int64.Type}, {"remoteIp", type number}, {"type", type text}, {"updatedAt", type datetimezone}, {"userAgent", type text}}),
    #"Expanded addressBillingCountry" = Table.ExpandTableColumn(#"Changed Type", "addressBillingCountry", {"code", "title"}, {"addressBillingCountry.code", "addressBillingCountry.title"}),
    ExpandZipcode = if #"Expanded addressBillingCountry"[addressBillingZipcode]{0} is table then Table.ExpandTableColumn(#"Expanded addressBillingCountry", "addressBillingZipcode", {"Element:Text"}, {"Address.Element:Text"}) else #"Expanded addressBillingCountry"
in
    ExpandZipcode

[Satch]

Even better!

Wonderful, thanks

[ImkeF]

What you do in the last step is to expand your main table and not the tables that sit in each row of your column [adressBillingZipcode].

So if you want to expand the table in each row of that column, your code needs to be constructed like so:

#"Added Custom" = Table.AddColumn(#"Expanded addressBillingCountry", "Zip", each if Value.Is([addressBillingZipcode], type table) then Table.ExpandTableColumn([addressBillingZipcode], "ColumnNameWithinThatTableToExpand", {"Element:Text"}, {"Address.Element:Text"}) else [addressBillingZipcode])

You need to adjust the "ColumnNameWithinThatTableToExpand" and check if the strings in Italic are correct.

[Satch]

Thanks,

this code gave however an error it could not find addressBillingZipcode.

In the meantime I saw Marcels solution which is even better than my question. Put it in the same column.

Thanks for your time!