Forum Discussion
Import a specific range from multiple excel files
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 columnsConnect 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.
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.
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 columnsConnect 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.