Create Table from nested tables containing one column each

[gmachowiak]

Hi,

I've got a working solution for my problem but it seems a bit excessive/overkill.

What i have is a table with two columns: LP, ColumnValues;
:: Column LP contains column names
:: ColumnValues contains tables with one column (named as in outer tables corresponding record in LP column), each table has the same number of rows.

Like: 

#table(
    {"LP","ColumnValues"},
    {
        {"Co1",#table({"Co1"},{{1},{2},{3}})},
        {"Co2",#table({"Co2"},{{10},{20},{30}})},
        {"Co3",#table({"Co3"},{{100},{200},{300}})}
    }
)

what i need to do is to create a single table from the ColumnValues column, where

:: first row contains first records from each of the nested tables

:: second row from second rows etc. like

#table(
    {"Co1","Co2","Co3"},
    {
        {1,10,100},
        {2,20,200},
        {3,30,300}
    }
)

My solution:

let
    //define table
    Source = 
        #table(
            {
            "LP","ColumnValues"
            },
            {
                {"Co1",#table({"Col1"},{{1},{2},{3}})},
                {"Co2",#table({"Col2"},{{10},{20},{30}})},
                {"Co3",#table({"Col3"},{{100},{200},{300}})}
            }
        ),
    //create table with a column containing indexes of records
    tableRowCount = Table.RowCount(Source[ColumnValues]{0}),
    Rows = List.Generate( () => 0, each _ < tableRowCount, each _+1),
    RowsToTable = Table.FromList(Rows, Splitter.SplitByNothing(), {"RowIndex"}, null),

    //add column containing table with first,second,thrid records from each of the nested columns
    addColumnTable = Table.AddColumn(RowsToTable, "Table",
        each 
        let
            i = [RowIndex],
            src=Source,
            transform = Table.TransformColumns(
                src,
                {
                    "ColumnValues",
                    each 
                    let 
                        RowValue = Table.Column(_,Table.ColumnNames(_){0}){i}
                    in
                        RowValue
                }
            ),
            Pivoted = Table.Pivot(transform, List.Distinct(transform[LP]), "LP", "ColumnValues")
        in
            Pivoted
    ),
    //expand tables
    tableFinal = Table.Combine(addColumnTable[Table])
in
    tableFinal

 it seems to be calc heavy as it iterates throguh source for every row

Considerations:

:: each nested table will alaways contain one column and all tables will have the same number of rows but the number may vary each time query is run);

:: there might be different number of rows in Source query  -> effectively different number of columns in output table

[dufoq3]

Hi @gmachowiak, different approach here.

Result

let
  Source = #table(
      {"LP","ColumnValues"},
      {
          {"Co1",#table({"Co1"},{{1},{2},{3}})},
          {"Co2",#table({"Co2"},{{10},{20},{30}})},
          {"Co3",#table({"Co3"},{{100},{200},{300}})}
      }
),
    ColumnvaluesToList = Table.TransformColumns(Source, {{"ColumnValues", each Table.Column(_, Table.ColumnNames(_){0})}}),
    ToColumns = Table.FromRows(List.Zip(ColumnvaluesToList[ColumnValues]), ColumnvaluesToList[LP])
in
    ToColumns

[ThxAlot]

let
    Source = #table(
    {"LP","ColumnValues"},
    {
        {"Co1",#table({"Co1"},{{1},{2},{3}})},
        {"Co2",#table({"Co2"},{{10},{20},{30}})},
        {"Co3",#table({"Co3"},{{100},{200},{300}})}
    }
),
    Cols = Table.ToColumns(Source),
    Transformed = #table(Cols{0}, List.Zip(List.Transform(Cols{1}, each Table.ToColumns(_){0})))
in
    Transformed

[ronrsnfld · Accepted solution]

Try this:

let
    //define table
    Source = 
        #table(
            {
            "LP","ColumnValues"
            },
            {
                {"Co1",#table({"Col1"},{{1},{2},{3}})},
                {"Co2",#table({"Col2"},{{10},{20},{30}})},
                {"Co3",#table({"Col3"},{{100},{200},{300}})}
            }
        ),

    Columns = List.Accumulate(
        Source[ColumnValues],
        {},
        (s,c)=> s & Table.ToColumns(c)),

    Result = Table.FromColumns(Columns, Source[LP])
in 

    Result