Expand each table dynamically from list, using list sequence as equivalence for like columns

[Mitsugu]

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	Column1 Column2 Column3
S1, 2	Column2 Column3 Column5 Column7

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.

[wdx223_Daniel]

NewStep=Table.FromColumns(List.Zip(List.TransformMany(Table.ToRecords(PreviousStepName),each Table.ToRows(Table.SelectColumns([Custom],[List])),(x,y)=>y)))

[Mitsugu]

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?

[wdx223_Daniel · Accepted solution]

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")

[Mitsugu]

Thanks Daniel!

Took me a while to get but I got your formula working now~

= Table.CombineColumns(SourcetoExpand2,{"Custom", "List"},each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(_{0},_{1})), 1),"Custom")