Forum Discussion
Power Query Editor Dynamic SharePoint Links
Hi Anonymous & serpiva64 - I would encourage you to simply the Power Query by introducing Table.SelectRows filter after the Source step using Text.Contains for the Folder Name filter. This will make it easy to manage the parameters passed from outside the query. The M advanced editor will look more like this.
let Source = SharePoint.Files(#"SharePoint Site", [ApiVersion = 15]), Folder = Table.SelectRows(Source, each Text.Contains( [FolderPath] , #"SharePoint Folder") ), File = Table.SelectRows(File, each [FolderPath] = #"SharePoint File"), #"Open Excel" = Excel.Workbook( File{0}[Content] ) in #"Open Excel"
This should make it possible to pass parameters to update the SharePoint Site and Folder and
I dont quite understand.
I have copied the advanced editor structure below, where the FILENAME will remain the same, but the SHAREPOINT_FILE_PATH changes. It has worked with the main SharePoint Link which is set as SharePoint Link.
But I do not know how to format the SharePoint File Path sections so I dont get error messages.
let
Source = SharePoint.Files(#"SharePoint Link", [ApiVersion = 15]),
#"FILENAME xlsx_SHAREPOINT_FILE_PATH" = Source{[Name="FILENAME xlsx_SHAREPOINT_FILE_PATH"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"FILENAME xlsx_SHAREPOINT_FILE_PATH"),
#"Incident Records_Sheet" = #"Imported Excel Workbook"{[Item="Incident Records",Kind="Sheet"]}[Data]
Hi Anonymous & serpiva64 - I would encourage you to simply the Power Query by introducing Table.SelectRows filter after the Source step using Text.Contains for the Folder Name filter. This will make it easy to manage the parameters passed from outside the query. The M advanced editor will look more like this.
let Source = SharePoint.Files(#"SharePoint Site", [ApiVersion = 15]), Folder = Table.SelectRows(Source, each Text.Contains( [FolderPath] , #"SharePoint Folder") ), File = Table.SelectRows(File, each [FolderPath] = #"SharePoint File"), #"Open Excel" = Excel.Workbook( File{0}[Content] ) in #"Open Excel"
This should make it possible to pass parameters to update the SharePoint Site and Folder and