How to import specific cells from several Excel files

O

omezquita

New member
Joined
Apr 1, 2016
Messages
19
Reaction score
0
Points
0
Website
www.masterdataanalysis.com
Excel Version(s)
2016
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:

FilenameValue of A35Value of B57Value of D12Value of K86
...............
...............

Is this possible in PQ?

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

Thanks!
 
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.

Code: 
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"
 
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.
 

Attachments

  • PullSpecificCells.xlsx
    16.7 KB · Views: 23
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!
 
You must log in or register to reply here.
Back
Top