We would like to propose an enhancement to improve efficiency when working with SharePoint data sources. Currently, when connecting to a SharePoint site, all available files are loaded, and users m...
Hey I 100% agree with this idea. Now it is unnecessarily difficult to get data from Large Sharepoint Sites. For the original Poster: There ARE quicker (but not easier) ways to get data from Sharepoint in a quick way. I've created this Query that basically reads files from a sharepoint folder using the API (note if you want a single file you can use only the last bit). This Query should refresh as well in the PBI Service so you can auto-refresh (which other solutions I found online didn't). Anyway you can try it:
let
SharepointURL = "https://[mycompany].sharepoint.com",
SharepointSite = "/sites/[myCompanySharepointSite]/",
RelativeFolderPath = "[FirstFolderName]/[SubFolderName]/.../[LastSubfoldernameAndNoTrailingSlashes]",
Source = Xml.Tables( Web.Contents( SharepointURL&SharepointSite&"_api", [ RelativePath = "web/GetFolderByServerRelativeUrl('"&SharepointSite&RelativeFolderPath&"')/Files", IsRetry = true ] ) ), entry = Source{0}[entry],
#"Changed Type" = Table.TransformColumnTypes( entry, { {"id", type text}, {"title", type text}, {"updated", type datetime} } ),
#"Renamed Columns" = Table.RenameColumns( #"Changed Type", { {"title", "Name"}, {"updated", "Date modified"} } ),
#"Added ServerRelativeUrl" = Table.AddColumn( #"Renamed Columns", "ServerRelativeUrl", each Text.BetweenDelimiters([id], "decodedurl='", "')"), type text ),
#"Added Folder Path" = Table.AddColumn( #"Added ServerRelativeUrl", "Folder Path", each SharepointURL&SharepointSite&RelativeFolderPath&"/", type text ),
#"Added Extension" = Table.AddColumn( #"Added Folder Path", "Extension", each if Text.Contains([Name], ".") then "." & Text.AfterDelimiter([Name], ".", {0, RelativePosition.FromEnd}) else null, type text ),
#"Added Custom" = Table.AddColumn(#"Added Extension", "FileName", each Text.AfterDelimiter([id],"/",{0, RelativePosition.FromEnd})),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","')","",Replacer.ReplaceText,{"FileName"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([FileName] <> "PAKKETTEN v0.1.xlsx")),
#"Added Attributes" = Table.AddColumn( #"Filtered Rows", "Attributes", each [Hidden = false], type record ),
#"Added Content" = Table.AddColumn( #"Added Attributes", "Content", each Web.Contents( SharepointURL&SharepointSite&"_api", [ RelativePath = "web/GetFileByServerRelativePath(decodedurl='" & [ServerRelativeUrl] & "')/$value", IsRetry = true ] ), type binary ) in #"Added Content"
Note I'm not 100%sure on the 'isRetry = true' part. I think it might lead to unneccesary extra API calls but I thought it prevented with stale data. (Maybe someone Else in the community can help with this)
