Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Even better!
Wonderful, thanks
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |