Forum Discussion
Expand each table dynamically from list, using list sequence as equivalence for like columns
I'm looking for a command similar to the Table.ExpandTableColumn() with a change to the required column extraction for each table.
= Table.ExpandTableColumn(Source, "ColumntoExpand", ColumnfromTable, ColumnfromTableRenamed)
I have your typical grouped data in [Name] and [Custom] meaning all column names in each Table cell will be the same.
However, the data within is not organised and the essence is;
| Column data | Name and Index.2 | Actual Column in Table Cell |
| Date | S1, 1 | Column1 |
| S1, 2 | Column5 | |
| S1, 3 | Column6 |
bassically what I have.
I do, however, have a list of column names in [List]
| Name and Index | List | ||||
| S1, 1 |
| ||||
| S1, 2 |
|
Column1 from "S1, 1" holds the same data as Column2 in "S1,2" and I would like to extract and treat them as the same column. The list will not always have the same amount of columns extract and I can create a new named list as the column headers for it if required.
then transform the custom column firstly, then click to expand it.
NewStep=Table.CombineColumns(PreviousStepName,{"Custom","List"},each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(_{0},_{1}))),"Custom")
4 Replies
NewStep=Table.FromColumns(List.Zip(List.TransformMany(Table.ToRecords(PreviousStepName),each Table.ToRows(Table.SelectColumns([Custom],[List])),(x,y)=>y)))
Thank you so much, it works great!
I did notice though that the formula only looks at the [Custom] column and doesn't retain any of the information from the other columns. Is there a way to retain that information while expanding the tables?
then transform the custom column firstly, then click to expand it.
NewStep=Table.CombineColumns(PreviousStepName,{"Custom","List"},each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(_{0},_{1}))),"Custom")
