How to import specific cells from several Excel files

[omezquita]

How do I import specific cells from a bunch of Excel files in a folder.

For example, let's say I have 100 Excel files in a folder and I want to import cells: A35, B57, D12, K86.

At the end I'd like an arrangement like:

Filename	Value of A35	Value of B57	Value of D12	Value of K86
...	...	...	...	...
...	...	...	...	...

Is this possible in PQ?

Please notice that cells are not named within the Excel files.

Thanks!

 

[Ken Puls]

Yes, it's possible, here's one method to get the data from a single file. Test this first, and see if it gets the right data for you, then we can turn it into a function and use it for each file.

let    Source = Excel.Workbook(File.Contents("C:\Tests\Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Sheet1_Sheet,{"Column1", "Column2", "Column4", "Column11"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "A"}, {"Column2", "B"}, {"Column4", "D"}, {"Column11", "K"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "A35", each if [Index]=35 then [A] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B57", each if [Index]=57 then [B] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "D12", each if [Index]=12 then [D] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "K86", each if [Index]=86 then [K] else null),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"A35", "B57", "D12", "K86"}),
    #"Filled Up" = Table.FillUp(#"Removed Other Columns1",{"A35", "B57", "D12", "K86"}),
    #"Kept First Rows" = Table.FirstN(#"Filled Up",1)
in
    #"Kept First Rows"

 

[Brian Smith]

Here is another method.

Assumes the excel workbooks are stored in a folder called "files" and that you want to pull the values in the cells you mentioned from each file.

 

[omezquita]

Both methods work very good.

Is there a way to generalize this? For example, having a query that I pass the desired range (e.g.: "A4, B10:B14") and the code will separate the cells and extract the data from each file.

With both methods I need to create a new line of M code for each cell to be extracted.

Thanks!