Error at first value while using list.generate in power query

[siva_powerbi]

I am trying to extract the value from list and append data for each value in list in a loop.

Using list.Generator to achieve this task, problem I am facing here is for first value in list always returns error and remaining values are correct. Unable to resolve the error.

Code:

let
Source = Excel.Workbook(File.Contents("C:\Users\Sivakumar A\Desktop\Power BI\test1.xlsx"), null, true),
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Test1", type text}, {"Test2", Int64.Type}, {"Test3", Int64.Type}, {"Test4", Int64.Type}}),
#"Col Names" = Table.ColumnNames(#"Changed Type"),
#"Generate" = List.Generate(() => [i=0,x= Table.FromList(#"Col Names",Splitter.SplitByNothing)],
each [i]<4,
each [
// x={i},
i=[i]+1,
x="Table.Distinct(Table.FromList("&#"Col Names"{i}&", Splitter.SplitByNothing(), null, null, ExtraValues.Error))"

],
each [x])
in
Generate

I have 4 rows in list, first row throws error.

Output:

Expression.Error: 1 arguments were passed to a function which expects 0.
Details:
Pattern=
Arguments=[List]

Unable to solve, kindly help

Values in #"Col Names"

[Anonymous]

In Power Query, if your query has rows with data errors, you can remove these rows from your query. Data errors can occur as a result of a query or step evaluation, such as changing the type of a column to Number that results in errors for some cells.

Power Query if Statements

1. The 'if' in Power Query is lower case.
2. Instead of commas separating the value_iaf_true and value_if_false arguments we have the words then and else (in lower case).
3. null means blank in Power Query.
4. Power Query formulas are case sensitive.

[CNENFRNL]

Hi, @siva_powerbi , I just help correct the syntactic error in that expression; but what is the expected result in the first row?

[siva_powerbi]

Expected output is first row should be

Table.Distinct(Table.FromList(Sheet2 (2)[Test1], Splitter.SplitByNothing(), null, null, ExtraValues.Error))

instead of TABLE.

Thanks

[CNENFRNL · Accepted solution]

Hi, @siva_powerbi , I think the glitch lies in here

() => [i=0,x= Table.FromList(#"Col Names",Splitter.SplitByNothing)]

try changing it to

() => [i=0,x= Table.FromList(#"Col Names",Splitter.SplitByNothing())]

[siva_powerbi]

Thanks for the reply.

Error gone but new issue, First I am seeing a TABLE retuned with full set of values in the list.

Can you please help why this change?