Forum Discussion
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
- ronrsnfldSuper User
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 - ThxAlotSuper User
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 - dufoq3Community 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