Expand value from table

[valeriminakov]

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 want to expand it. These tables contain just one value - just year. I've tried to use such fuctions as Text.FromBinary or Table.ExpandTableColumn but I got errors.

How can I expand these some table values?

Thanks.

[MarcelBeug · Accepted solution]

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.

[v-sihou-msft]

@valeriminakov

It's quite strange that you keep the table and text into same column since there's no Expand icon on the column header. I can't reproduce this scenario. Can you share your XML file?

Regards,

[valeriminakov]

Yep @v-sihou-msft, this file.

List of accident (11-12/2016)

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!

[MarcelBeug]

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 _}})

[valeriminakov]

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. 

[MarcelBeug · Accepted solution]

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

[valeriminakov]

@MarcelBeug thank you for your help! 

[MarcelBeug · Accepted solution]

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.

[twalshacx]

@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]

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

[MarcelBeug]

You can use Table.FirstValue, like in:

let
    Table = #table(type table[Value = any],
        {{"2000"},
         {#table(1,{{"2001"}})},
         {#table(1,{{"2002"}})},
         {"2003"}}),
    ValueFromTable = Table.TransformColumns(Table, {{"Value", each if _ is table then Table.FirstValue(_) else _}})
in
    ValueFromTable