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,

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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