Can't save a dynamic function within Dataflow to access excel workbook in SharePoint

[be_better]

Context
I have several Excel (.xlsm) files located within a SharePoint folder

I am creating a Function that extracts the Worksheet names from each File. The Function works by looking at the filenames and then using that as a dynamic parameter at the end of the SharePoint URL path.

Issue

On desktop I have downloaded the folder of files and it works great. Within Dataflows, the function works as intended..great but I 

Can't save dataflow due to the error: "One or more tables references a dynamic data source"
I've also attempted RelativePath but it repeatedly stuck on a loop to ask me Credentials (although the url is correct)

Solution Attempt 1
Here is the regular function, which does work However when attempting to save, results in issue:  "Can't save dataflow" "One or more tables references a dynamic data source".

let
    Source = (file_name as any) => let
    Source = Excel.Workbook(Web.Contents("https://Client.sharepoint.com/sites/Host/Shared%20Documents/General/XXX/XXX/" & file_name), null, true),
  #"Removed other columns" = Table.SelectColumns(Source, {"Name"}),
  #"Filtered rows 1" = Table.SelectRows(#"Removed other columns", each Text.Contains([Name], "Sheet Name"))
in
  #"Filtered rows 1"

Solution Attempt 2

tried Relativepath

results in issue of repeaedly asking "The credentials provided for the Web source are invalid", & I configure my connection.

even though the URL is spits out does work

I also tried paramaterising the  "https://Client.sharepoint.com/sites/Host/" & "Shared%20Documents/General/XXX/XXX/"

(file_name as any) => let
  Source = Excel.Workbook(Web.Contents("https://Client.sharepoint.com/sites/Host/"
  ,[RelativePath="Shared%20Documents/General/XXX/XXX/" & file_name]), null, true),
  #"Removed other columns" = Table.SelectColumns(Source, {"Name"}),
  #"Filtered rows 1" = Table.SelectRows(#"Removed other columns", each Text.Contains([Name], "Sheet Name"))
in
  #"Filtered rows 1"