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

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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's avatar
valeriminakov
Frequent Visitor
9 years ago

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!