Forum Discussion

KJChin's avatar
KJChin
Frequent Visitor
1 year ago
Solved

Choose Column before Expand Table

I am importing data from multiple excel files in sharepoint folder to Power Query. I would like to define the chosen columns before expand the data. Previously I have defined to take the data of first sheet of every excel file in the folder as the sheet name is not constant (it works). Same reason with the columns, the name sometimes changes, while the order is remain same. 

This is the query I done in 'Transform Sample File':

let
    Source = Excel.Workbook(Parameter1, null, true),
    FirstSheet = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

Below is the enhance version to cover the column, get error where it said:
Expression.Error: We cannot convert the value 1 to type Text.
Details:
Value=1
Type=[Type]

let

    Source = Excel.Workbook(Parameter1, null, true),
    FirstSheet = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true]),
    SelectedColumns = Table.SelectColumns(PromotedHeaders, {1, 2, 3, 7})

in
    SelectedColumns

  I did set the data type yet the error still there.

 

Any suggestions is much appreciated. Thank you.

  • Hi KJChin 

     

    ...
    Table.SelectColumns(PromotedHeaders, List.Transform({1, 2, 3, 7}, each Table.ColumnNames(PromotedHeaders){_ - 1}))

     Stéphane

2 Replies

  • Hi KJChin 

     

    ...
    Table.SelectColumns(PromotedHeaders, List.Transform({1, 2, 3, 7}, each Table.ColumnNames(PromotedHeaders){_ - 1}))

     Stéphane

    • KJChin's avatar
      KJChin
      Frequent Visitor

      OMG, IT WORKED! HAHA

      Thanks a lot, slorin You are amazing!