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

Pavan Gunda 0
Viewed 66

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"
powerquery
2 Answers

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://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns

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

userhiPt0

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}})
horseyride0
Related