Power Query Editor Dynamic SharePoint Links

[Anonymous]

Hello,
I currently have several files that link to a SharePoint to obtain their data, as shown in the screenshot the first line has the SharePoint link, and the other marked lines are the actual file path. (for data compliance I had to mark them so they are not viewable)

I need to change the file every update from a test environment to the production environment, meaning that the SharePoint changes too. I can manually alter the source links when that happens, but I want to make it dynamic using a variable.

Is it possible to define a variable in the Power Query Editor to have that variable in each file source and equal a link, and I can change the link once for the new environment and it will automatically change it in all of the files I am sourcing in Power BI?

I hope this is clear what I am trying to accomplish, and I appreciate any support here! 

[Anonymous]

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]

[Daryl-Lynch-Bzy · Accepted solution]

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 

[serpiva64]

Hi, 

You can try creating a new parameter

then you use  it in your selection

= Table.SelectRows(Source, each ([Folder Path] = "https://XXXXXXXX.sharepoint.com/sites/Ambienteditest/Documenti condivisi/"&Parameter1&"/"))

Then you can change your parameter in Desktop, in Power Query Editor and also in the Service

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !