Forum Discussion

siva_powerbi's avatar
siva_powerbi
Icon for Helper IV rankHelper IV
5 years ago
Solved

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

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"

 

 

 

  • 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())]

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion

    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's avatar
      siva_powerbi
      Icon for Helper IV rankHelper IV

      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?

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion

    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's avatar
      siva_powerbi
      Icon for Helper IV rankHelper IV

      Expected output is first row should be

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

      instead of TABLE.

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.