Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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