Name not recognized error in List Generate

Question

My table has 10 columns, numbered StepDet 1 through StepDet 10. A table is integrated into each one. Each table should be expanded, but not with all of the columns. Only Step Name and Steps 1 through 10 need to be expanded.

I tried the code below, but I get an Expression error.

The name "prevTable" could not be spelled correctly. Make sure the spelling is accurate.

let
    // Load the query
    Source = Source_name,

    // Generate the list of columns to expand
    columnsToExpand = List.Generate(
        () => [i = 1],
        each [i] <= 10,
        each [i = [i] + 1],
        each "StepDet " & Text.From([i])
    ),

    // Expand the columns
    expandedTable = List.Last(List.Generate(
        () => [i = 0, prevTable = Source],
        each [i] <= List.Count(columnsToExpand)-1,
        each [
            i = [i] + 1,
            prevTable = Table.ExpandTableColumn(
                prevTable,
                columnsToExpand{[i]},
                {"Step Name", "ActionDet 1", "ActionDet 2", "ActionDet 3", "ActionDet 4", "ActionDet 5", "ActionDet 6", "ActionDet 7", "ActionDet 8", "ActionDet 9", "ActionDet 10"},
                List.Transform(
                    {"Step Name", "ActionDet 1", "ActionDet 2", "ActionDet 3", "ActionDet 4", "ActionDet 5", "ActionDet 6", "ActionDet 7", "ActionDet 8", "ActionDet 9", "ActionDet 10"},
                    each columnsToExpand{[i]} & "." & _
                )
            )
        ],
        each [prevTable]
    ))
in
    expandedTable

Answer 1

Try this:

let Source = <  >,
columnsToExpand = List.Transform({1 .. 10}, each "StepDet "&Text.From(_)) & {"Step Name"},
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source,  List.Difference(Table.ColumnNames(Source),columnsToExpand), "Attribute", "Value"),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(#"Unpivoted Columns", "Value"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded" = Table.ExpandTableColumn(#"Unpivoted Columns", "Value",ColumnsToExpand ,ColumnsToExpand )
in  #"Expanded"