CODEWITHSUNDEEP
powerquery
Pavan Gunda
Pavan Gunda

Reputation: 97

power query Expression.Error: We cannot convert a value of type Function to type List in power query

I am trying to run the query in power query editor and it failed with below error

enter image description here

where as the same query works fine in winserver 2016 and it fails with above error in win server 2019. Am I missing any thing ?

I did compared the settings and everything looks good.

let
  //Get data Report H1
  Source1 = Excel.Workbook(File.Contents("\\path\filename.xlsx"), null, true), 
  #"classic_Sheet1" = Source1{[Item = "classic", Kind = "Sheet"]}[Data], 
  #"Trimmed Text1" = Table.TransformColumns(#"classic_Sheet1", Text.Trim), 
  #"Third Row as Header1" = Table.PromoteHeaders(Table.Skip(#"Trimmed Text1", 2)), 
  #"Selected Columns1" = Table.SelectColumns(
    #"Third Row as Header1", 
    {" ID", " Status", "Customer Id ", "Agent", "Leg"}
  ), 

  //Get Report H2
  Source2 = Excel.Workbook(File.Contents("\\path\filename.xlsx"), null, true), 
  #"classic_Sheet2" = Source2{[Item = "classic", Kind = "Sheet"]}[Data], 
  #"Trimmed Text2" = Table.TransformColumns(#"classic_Sheet2", Text.Trim), 
  #"Third Row as Header2" = Table.PromoteHeaders(Table.Skip(#"Trimmed Text2", 2)), 
  #"Selected Columns2" = Table.SelectColumns(
    #"Third Row as Header2", 
    {" ID", "Status", "Customer Id ", "Agent", "Leg"}
  )

in
  #"Excluded IDs"

Upvotes: 0

Views: 19505

Answers (2)

horseyride
horseyride

Reputation: 21428

You want something along these lines

= Table.TransformColumns(#"classic_Sheet1",{{"ColumnNameHere", Text.Trim, type text}})

= Table.TransformColumns(#"classic_Sheet1"",{{"ColumnNameHere", Text.Trim, type text}, {"DifferentColumnNameHere", Text.Trim, type text}})

Upvotes: 0

Peter
Peter

Reputation: 12385

The error message is pointing you to the problem:
Table.TransformColumns expects a list as the second parameter,
while you are providing a function:

Table.TransformColumns(
   table as table, 
   transformOperations as list, 
   optional defaultTransformation as nullable function, 
   optional missingField as nullable number
) as table

Please read the official documentation here:
https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns

The issue has nothing to do with winserver 2016 or winserver 2019.

Upvotes: 1

Related Questions