Forum Discussion

valeriminakov's avatar
valeriminakov
Frequent Visitor
9 years ago
Solved

Expand value from table

Hi guys! Can you help me please?   So i've got some XML file then I expanded it. Here I've got one of columns (year of manufacture) and a lot of values. But I've got some 'table' values and I wan...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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's avatar
    MarcelBeug
    9 years ago

    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")

     

twalshacx's avatar
twalshacx
Regular Visitor
8 years ago

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

twalshacx's avatar
twalshacx
Regular Visitor
8 years ago

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..