Power Query a SharePoint folder

Hi luisgaruz 

As you've stated, SharePoint.Contents connects to the files in a SharePoint folder, but doesn't provide additional columns that may have been added in SharePoint.

 

One way of accessing this additional columns is connecting to the SharePoint folder using the OData.Feed

function to connect to the SharePoint site.

 

I have attached a PBIX where I set up these queries to connect to a specified SharePoint folder, navigate to a specified folder, and return a table listing the files specified columns added (in my example a column called "MyText" and "DueDate").

 

Since the M code is not too long, I've pasted it all below. Each query's name is shown as a comment.

• SiteURL: SharePoint Online site URL
• FolderPath: the folder path within the site
• FolderPathList: Splits the folder path into items (delimited by "/")
• ListColumnsRequired: The additional "attribute" columns needed from the folder
• SharePoint Files Staging: Initial query containing just the list of files, using SharePoint.Contents function.
• SharePoint File Attributes: This query retrieves the values of the attribute columns using OData.Feed function.
• SharePoint Files: Joins the previous two queries to produce final table.

 

 

// SiteURL
"https://vchwv.sharepoint.com/sites/OwenTestSite" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// FolderPath
"Shared Documents/MyWorkbooks" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// FolderPathList
let
    Source = Splitter.SplitTextByDelimiter("/")(FolderPath)
in
    Source

// ListColumnsRequired
let
    Source = {"MyText", "DueDate"}
in
    Source

// SharePoint Files Staging
let
    Source = SharePoint.Contents(SiteURL, [ApiVersion = 15]),
    NavigateToFolder =
        List.Accumulate(
            FolderPathList,
            Source,
            (state,current) => state{[Name=current]}[Content]
        ),
    #"Remove Folders" = Table.SelectRows(NavigateToFolder, each [Attributes][Kind] <> "Folder"),
    #"Inserted Merged Column" = Table.AddColumn(#"Remove Folders", "File URL", each Text.Combine({[Folder Path], [Name]}), type text)
in
    #"Inserted Merged Column"

// SharePoint File Attributes
let
    Source = OData.Feed( SiteURL & "/_api/web/lists", null, [Implementation="2.0"]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([EntityTypeName] = "Shared_x0020_Documents")),
    Items = #"Filtered Rows"{0}[Items],
    #"Select Required Columns" = Table.SelectColumns(Items,{"File"} & ListColumnsRequired),
    #"Expanded File" = Table.ExpandRecordColumn(#"Select Required Columns", "File", {"LinkingUrl"}, {"File URL"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded File", each [File URL] <> null and [File URL] <> ""),
    #"Clean URL" = Table.TransformColumns(#"Filtered Rows1", {{"File URL", each Text.BeforeDelimiter(_, "?", {0, RelativePosition.FromEnd}), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Clean URL",{{"MyText", type text}, {"DueDate", type date}})
in
    #"Changed Type"

// SharePoint Files
let
    Source = #"SharePoint Files Staging",
    #"Merged Queries" = Table.NestedJoin(Source, {"File URL"}, #"SharePoint File Attributes", {"File URL"}, "SharePoint Files Attributes", JoinKind.LeftOuter),
    #"Expanded SharePoint Files Attributes" = Table.ExpandTableColumn(#"Merged Queries", "SharePoint Files Attributes", ListColumnsRequired),
    #"Select Required Columns" = Table.SelectColumns(#"Expanded SharePoint Files Attributes",{"Name", "Extension", "Date modified", "Date created", "File URL", "MyText"})
in
    #"Select Required Columns"

 

 

I've tested this refreshes sucessfully in the Power BI Service with OAuth credentials.

 

Note: The OData.Feed query can likely be optimized, and the Microsoft Graph may also be an option.

I would consider this a proof of concept and hopefully enough for you to get started 🙂

 

Regards