Name not recognized error in List Generate

0 votes

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
Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
435 views

1 answer to this question.

0 votes

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"
answered Feb 16, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

HTTP Error 403.14 - Forbidden - The Web server is configured to not list the contents of this directory

Try keeping this into your web config ...READ MORE

answered Feb 11, 2022 in Others by Rahul
• 9,670 points
11,447 views
0 votes
1 answer

error: ORA-65096: invalid common user or role name in oracle

To start with, the oerr utility is ...READ MORE

answered Feb 16, 2022 in Others by Aditya
• 7,680 points
16,914 views
0 votes
1 answer

'composer' is not recognized as an internal or external command in windows server

For anyone coming here from Google who ...READ MORE

answered Feb 17, 2022 in Others by Aditya
• 7,680 points
18,146 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,848 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,445 views
0 votes
1 answer

Format Data to tabular structure in Excel - Power m Query - Excel

Hi, @Kiranbvsn You can check this out: https://www.howtoexcel.org/ ...READ MORE

answered Oct 27, 2020 in Power BI by Gitika
• 65,910 points
441 views
0 votes
1 answer

Power query to return true / false if multiple columns are either = or null

Add column, custom column with formula: = try ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,420 points
1,771 views
0 votes
1 answer

Ceon SEO URl gives the error page not found in zen cart

in the admin section there is  an ...READ MORE

answered Feb 18, 2022 in Others by narikkadan
• 63,420 points
273 views
0 votes
1 answer

Ceon SEO URl gives the error page not found in zen cart

 go to admin->Modules->Ceon URI Config, select Installation ...READ MORE

answered Feb 26, 2022 in Others by narikkadan
• 63,420 points
545 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP