Hello - yes, I understand. It is the different folders that are causing the dynamic data source error. I realize the solution in the link provided is much more complex and does more than you are asking. Here is another option...
This is the line that is your problem:
source = SharePoint.Files("https://xxxx.sharepoint.com/sites/" & sharePointURL, [ApiVersion = 15]),
In order to avoid the dynamic data source error that occurs in dataflows, you will need to first query the SP root site, then handle the subfolders in different steps. Dataflows does require that all of the files you wish to query be contained in the same root site - the only way around that one is to have separate queries for each root site and then append them.
Add three text parameters to your dataflow:
- paramSharepointRootUrl
- paramSharepointDocsDirectory
- This is the text that appears between your SP site root URL and the next forward slash. It is typically "Documents" (for private sites) and "Shared Documents" (for sites that are not private).
- paramSharepointFileName
- If all of the files have the same file name, add it as a parameter.
Add a new query that connects to the root Sharepoint Url. This query's loading should be disabled (becuase it will contain complex column types). In my dataflow my query is named source_RootFolderContent.
let
Source = SharePoint.Files(paramSharepointRootUrl, [ApiVersion = 15])
in
Source
Next, add another query like the example below. We may need to tweak this further based on your needs. I recommend adding it to a query as it is and work through each step. After you have confirmed that the correct set of files are being returned, we can convert it into a function and remove any steps that are not needed for your scenario.
let
// START: Declare variables
//
varDocsDirectory = Text.Lower ( paramSharepointDocsDirectory),
varFileName = Text.Lower ( paramSharepointFileName ),
// If you want to filter the files for a specific date range, you can create additional parameters and use them like this.
// If the latest date is before the earliest date, use the earliest date.
// varMaxDateForBinaries = if LatestFileModificationDate < EarliestFileModificationDate then EarliestFileModificationDate else LatestFileModificationDate,
// If you'd like to only look for files in a specific set of subfolders, you can specify another parameter and use it like in these next few steps:
// Remove the specified character from the start of the string.
// ParentFolders_TrimCharFromStart =
// if Text.StartsWith ( SharepointParentFolders, "/" )
// then Text.TrimStart ( SharepointParentFolders, 1 )
// else SharepointParentFolders,
// Remove the specified character from the end of the string.
// ParentFolders_TrimCharFromEnd =
// if Text.EndsWith ( ParentFolders_TrimCharFromStart, "/" )
// then ParentFolders_TrimCharFromStart
// else ParentFolders_TrimCharFromStart & "/",
//Final variable
// varParentFolders = ParentFolders_TrimCharFromEnd,
//
// END: Declare Variables
source = source_RootFolderContent,
// Add a column for the subfolder paths (extracted from the file paths that are returned in the table.
// This and some of the other steps below may not be required in all scenarios. It is primarily used as a helper column to check a subset of subfolders, in the event the target name appears in more than one subfolder level, like this - /first folder name/target folder name/another subfolder/target folder name
add_subfolders = Table.AddColumn(Source, "Subfolders", each Text.BetweenDelimiters([Folder Path], "/sites/", "/", 0, 0), type text),
// Add a column for the Sharepoint documents directory.
add_dir = Table.AddColumn(add_subfolders , "Directory", each Text.BetweenDelimiters([Folder Path], [Subfolders] & "/", "/", 0, 0), type text),
// Count the number path separators that exist between the directory and the file name.
// This is only needed if you are wanting to perform validation on the number of subfolders.
add_folder_delimiter_count = Table.AddColumn(
add_dir,
"Delimiters Btwn Dir and End",
each List.Count ( Text.PositionOfAny ( [Folder Path], {"/"}, Occurrence.All ) ) + 1,
Int64.Type
),
// Extract the remaining subfolders in the path.
add_parent_folders = Table.AddColumn (
add_folder_delimiter_count,
"Parent Folders",
each Text.BetweenDelimiters(
[Folder Path],
[Directory] & "/",
"/",
0,
[Delimiters Btwn Dir and End]
), type text
),
remove_folder_path = Table.RemoveColumns(add_parent_folders, {"Folder Path", "Delimiters Btwn Dir and End"}),
// Keep only files that are in the specified directory with the specified parent folders.
// When selecting rows in a Sharepoint directory in dataflows, the filter must begin
// after the directory in order to avoid the dynamic datasource error message.
// If you don't need to look for specific files or folders then this is not needed.
filter_files = Table.SelectRows(
remove_folder_path,
each [Attributes]?[Hidden]? <> true
and Text.Contains ( [Directory], varDocsDirectory, Comparer.OrdinalIgnoreCase )
// and Text.Contains ( [Parent Folders], varParentFolders, Comparer.OrdinalIgnoreCase )
and Text.Contains ( [Name], varFileName, Comparer.OrdinalIgnoreCase )
),
// Filter the files by the Date Modified. (optional)
// select_files_by_date_modified = Table.SelectRows (
// filter_files,
// each [Date modified] >= EarliestFileModificationDate and [Date modified] <= varMaxDateForBinaries
),
// Optionally limit the results to the first N files.
// first_n_files = if FirstNFiles = null then select_files_by_date_modified else Table.FirstN ( select_files_by_date_modified, FirstNFiles ),
// Add a unique ID (integer) for each file. (optional)
add_id = Table.AddIndexColumn(filter_files, "FileId", 1, 1, Int64.Type)
in
add_id
