Forum Discussion
Expand value from table
Even better:
#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then Table.FirstValue(_, null) else _}})No need for "try ... otherwise", as a default value can be supplied as second argument that will be returned if the table is empty.
If all tables are either empty or have 1 column, then you can:
first transform all values to lists and
next use the expand button to expand the column with embedded lists.
Generated code:
#"Lists from ndu" = Table.TransformColumns(#"Expanded infoDtp",{{"ndu", each if _ is table then Table.ToList(_) else {_}}}), #"Expanded ndu" = Table.ExpandListColumn(#"Lists from ndu", "ndu")
Yep v-sihou-msft, this file.
This is open data from russian traffic accident statistics. I guesse it may be a lot of bugs because it takes from differents sources.
To find my example first you need to expand columns: 'tab' then 'infoDtp' then 'ts_info' and finaly it will be column 'g_v' (year of manufactured).
Thank You!
After your steps, there are 8 tables in column g_v, of which 7 are empty.
You can expand the table with the following code (In which #"Expanded ts_info" is the name of the previous step):
#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then try Table.FirstValue(_) otherwise null else _}})Even better:
#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then Table.FirstValue(_, null) else _}})No need for "try ... otherwise", as a default value can be supplied as second argument that will be returned if the table is empty.
MarcelBeug Hi Marcel,
I'm wondering if you can help with this as well, I have a problem where I'm calling data from an API, the API brings back some columns as tables. For agruments sake the column in quesiton is called Field Value I expand the column and I get in some cases text values and in others I get another table.
If I expand the column again the rows with text throw up an error, I've tried to follow what you've provided as the solution but I'm just not sure where to add it (i'm assuming it's in the advanced editor section of power query). What I want to happen is that the table values get expanded and those which are text values stay there and the expanded function on this row is skipped over (to stop the error).
I'd be deeply grateful for any help you could give.
Regards,
Tom
The M code in question is:-
#"Expanded get_mail_data.CustomFields.CustomField" = Table.ExpandTableColumn(#"Expanded get_mail_data.CustomFields", "get_mail_data.CustomFields.CustomField", {"Label", "Value"}, {"get_mail_data.CustomFields.CustomField.Label", "get_mail_data.CustomFields.CustomField.Value"}),
ExpandColumn1 = Table.ExpandTableColumn(#"Expanded get_mail_data.CustomFields.CustomField", "get_mail_data.FromUserDetails", {"Name"}, {"get_mail_data.FromUserDetails.Name"}),
ExpandColumn2 = Table.ExpandTableColumn(ExpandColumn1, "get_mail_data.CustomFields.CustomField.Value", {"Element:Text"}, {"get_mail_data.CustomFields.CustomField.Value.Element:Text"}),
When I do ExpandColumn1 I get the mixed results of some Text and some Tables, when I do ExpandColumn2 I can get all the text values that were tables from step ExpandColumn1 but all of the text values through up an error..
Thank you MarcelBeug so much!
Yep, I've got a lot of empty tables on it, but some have values. When I aggregate more then 100 XML files I realy can lose some important information.
Okay, if I got just one value in such tables I can use Table.FirstValue.
But when I have more then one values in such tables how can i expand all of them?For example in column -> 'tab' then 'infoDtp' then 'ndu'. In this column I've got already one or more values in one such table.
If all tables are either empty or have 1 column, then you can:
first transform all values to lists and
next use the expand button to expand the column with embedded lists.
Generated code:
#"Lists from ndu" = Table.TransformColumns(#"Expanded infoDtp",{{"ndu", each if _ is table then Table.ToList(_) else {_}}}), #"Expanded ndu" = Table.ExpandListColumn(#"Lists from ndu", "ndu")MarcelBeug thank you for your help! :smileyhappy:
