How to get data from within (many) documents in SharePoint library with Power Query

Question

Power BI junior here

How to look in each excel file from a SharePoint list and extract contents from predefined cells.

I am currently accessing a few intranet Sharepoint libraries containing .xlsx files and with the metadata of those files I am doing some reporting. For example, a library contains 10 excel files so I can graph who uploaded them, when they were uploaded, and wat category they were assigned to...

However, is there a way with Power Query to look into each and every of the files, take the value from, say cell A1 of the excel, and add it as a new column "CellA1Content"? I.e., make your own metadata from the content of the files and add them to the imported metadata table.

I've found some functions that I possibly might need:

File.Contents
Excel.CurrentWorkbook

However I am not well-versed enough in Power Query to put it all together, if it's even possible at all. I would have to do a foreach operation of some kind.

Edit: Solution

This worked. I selected the first non-hidden sheet in the excel and I also made the function so that I can pass the column and row number.

Main query:

let
    Source = SharePoint.Contents("http://mysharepoint", [Implementation=null, ApiVersion=15]),
    ... ... ...
    
    //Open each excel and get cell D5
    #"AddedColumn1" = Table.AddColumn(#"Filtered Rows", "AddedColumn1", each GetCellContent([Content],4,5))
in
    AddedColumn1

Blank query in Power BI, called GetCellContent:

let
    Source = (binaryParameter,col,row) => let
        Source = Excel.Workbook(binaryParameter, null, false),
        UnhiddenSheets = Table.SelectRows(Source, each if [Hidden]=false and [Kind]="Sheet" then true else false),
        Sheet = UnhiddenSheets{0}[Data],
        Column = Table.SelectColumns(Sheet,{Text.Combine({"Column",Number.ToText(col)})}),
        Cell = Record.Field(Column{row-1},  Text.Combine({"Column",Number.ToText(col)}) )
    in
        Cell
in
    Source

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2021-02-03T17:45:37

You'll need a Function used in a column like this.

This is my local interpretation of your problem, without sharepoint. The same logic is shared though.

Main Query

let
    Source = Folder.Contents("YourDirectory"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Row1Col1", each PullRow1Col1([Content]))
in
    #"Added Custom"

PullRow1Col1:

let
    Source = (binaryParameter) => let
        Source = Excel.Workbook(binaryParameter, null, false),
        Sheet1_sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        Column1 = Sheet1_sheet{0}[Column1]
    in
        Column1
in
    Source

How to get data from within (many) documents in SharePoint library with Power Query

1 Answers