Eliminate unwanted columns while connecting to Excel Workbook

[smpa01]

Hi,

Is there a way to select only the columns I want while connecting to a Excel workbook. For eaxample, I am connecting to  a workbook that contains the following columns with the following code.

Batch Number

Doc Type

Document Number

Company

G/L Date

Period Number

Fiscal Year

Supplier Name/ Explanation

Amount

Column1

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Test.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Batch Number", Int64.Type}, {"Doc Type", type text}, {"Document Number", Int64.Type}, {"Company", Int64.Type}, {"G/L Date", type date}, {"Period Number", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Supplier Name/ Explanation", type text}, {"Amount", type number}, {"Column1", type text}})
in
    #"Changed Type"

If I only want Batch Number and Doc Type column is there a way to only select them in the following step.

Table1_Table

 Thank you in advance.

[v-piga-msft]

Hi @smpa01,

If I understand your scenario correctly that you only want to keep Batch Number and Doc Type column when you connect excel workbook?

If it is, you could go to Query editor and Click Choose Columns, then you could select the columns you want to keep.

You also could refer to the expression highlight in red.

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Test.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Batch Number", Int64.Type}, {"Doc Type", type text}, {"Document Number", Int64.Type}, {"Company", Int64.Type}, {"G/L Date", type date}, {"Period Number", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Supplier Name/ Explanation", type text}, {"Amount", type number}, {"Column1", type text}})
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Batch Number", "Doc Type"})
in
    #"Removed Other Columns"

Best  Regards,

Cherry

[smpa01 · Accepted solution]

@v-piga-msft

I figured it out

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Test.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Sheet1")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])
in
    #"Promoted Headers1"