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
Satch
Helper III
Helper III

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

 

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?

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

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

 

Specializing in Power Query Formula Language (M)

Even better!

Wonderful, thanks

ImkeF
Super User
Super User

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

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.