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

 

 

 

 

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

     

10 Replies

  • 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
    • 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!

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

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