Forum Discussion

gmachowiak's avatar
gmachowiak
Frequent Visitor
2 years ago
Solved

Create Table from nested tables containing one column each

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

  • 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

     

     

     

3 Replies

  • 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

     

     

     

  • 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
  • dufoq3's avatar
    dufoq3
    Community Champion

    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