Power Query a SharePoint folder

[luisgaruz]

I am importing various folders with different Excel files in them with Power Query.

I am using the following command:

SharePoint.Contents(url as text, optional options as nullable record) as table

as descibed in: https://learn.microsoft.com/en-us/powerquery-m/sharepoint-contents

This command doesn't import all the properties each file has, that are like "tags", and help differenciate the different types of documents and also to know if one file is the latest version or it si archived.

How can I export all this information wich is really useful for me?

Thank you.

[OwenAuger · Accepted solution]

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

[Anonymous]

Hi @luisgaruz 

Based on your description, you should the sharepoint.files() connector, it will return  the properties each file has, you can refer to the following link.

https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder#determine-the-site-url

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.