Expand multiple columns with prefix dynamically

[Stoner]

I have columns containing table cells in all columns after RangeID. The amount will not be fixed but I can grab a list of column names (done in previous steps). I am looking to expand these columns with the original column name as prefix.

Extra Info:

The columns in these table cells will clash with one other (i.e. [Y]{0} has "Column1" and [Z1] might also have "Column1" but they are not to be treated as the same column). The number of columns inside [Y], [Z1], [Z2], & [X] are also not fixed. All columns in [Y] are however, equal (i.e. 5 columns, same name). The same will go for the other columns.

All table cells aligned in the same row will have the same amount of rows (i.e. [Y]{0} has 6 rows, [Z1]{0} and [X]{0} will also have 6 rows). The amount of rows might differ if they are not on the same row.

---

Update: I have included the prefix but still need the dynamic expand of columns

= Table.ReplaceErrorValues(
            Table.TransformColumns(
            Source_Sheetdata,
            List.Transform(List.Combine({{"Y"}, Source_SheetZlist, {"X"}}),
            each {_, (x)=>Table.PrefixColumns(x, _)})),
        List.Transform(List.Combine({{"Y"}, Source_SheetZlist, {"X"}}), each {_, null}))

[wdx223_Daniel]

try this code after the step in your fisrt pic

NewStep=let a=Table.ColumnNames(PreviousStepName) in Table.Combine(Table.ToList(PrevousStepName,each Table.PromoteHeaders(Table.FromColumns(List.TransformMany(List.Zip({a,_}),each if _{1}=null then {} else if _{1} is table then Table.ToColumns(Table.DemoteHeaders(Table.PrefixColumns(_{1},_{0}))) else {_},(x,y)=>y)))))