Import a specific range from multiple excel files

[CMISAuthority]

I want to import multiple excel files and choose in advance a specific range of columns from the same sheet in all files.

range A:Z

What is the solution?

[mahoneypat]

Do those columns have the same names in all files?  

If so, you can just use the Combine & Transform feature which will automatically create a function behind the scenes that does that for all files based on what you do in the sample file.

If not, you'll need to learn to use the Table.ColumnNames() and List.FirstN() functions to dynamically get the list of the first N columns.  You would use that to both select them in a Removed Other Columns step, and then to rename them (so the data from all files can be combined) in a Renamed Columns step.

Pat

[CMISAuthority]

I will explain in more detail.
I have 100 excel files every month.
All files come with the same sheets and the same names.
I only import a certain sheet whose structure is the same in all the files.
In each sheet , the first column is empty, followed by 20 columns with information.
I only need the columns with the information.
At first glance, it does not bother me that the query will show me the first column blank, because it can be deleted.
The problem is that in some files the query displays the first column and in some of them it skips and displays the second column directly.
20-30 of the files are imported with the first column, even though it is empty.
About 70 other files are imported without the first column, just as I need.
This results in the query having a table on a table (100 tables), with some of the tables moving one column to the right.
I do not know why in some of the files it takes the first column. Maybe because there is color in the first column, or someone typed something in there and then deleted it.
How can you ask the query in a simple way to always get A: Z or B: Z.
But do not accept once like this and once like this.
I would be very happy to help,
We have been trying to solve this problem for several months and have not found a solution anywhere.

[v-yingjl · Accepted solution]

Hi @CMISAuthority ,

To apply it for all sheets in all files, suggest you to create a folder to store these excel files.

Create a custom function like this:

(tab) as table =>
let
    A = Table.SelectColumns(Table.PromoteHeaders(tab,[PromoteAllScalars = true]),{"cat","val"},MissingField.UseNull)
in
    A

//You can add you needed column names in {"",""} to select specific columns

Connect to the folder, get each sheet for each table, invoke the above custom function:

let
    Source = Folder.Files("C:\Users\Admin\Desktop\xx"),
    #"Added Custom" = Table.AddColumn(Source, "Workbook", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Workbook", "Name"}),
    #"Expanded Workbook" = Table.ExpandTableColumn(#"Removed Other Columns", "Workbook", {"Data"}, {"Workbook.Data"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Workbook", "Custom Function", each #"Custom Function"([Workbook.Data]))
in
    #"Invoked Custom Function"

Now you will get the filtered column tables as a new column, you can just keep this table column if you want to expand and combine the values in it as a new table.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.