How to enable dynamic source path using Sharepoint folder as data source?

[Anonymous]

I'm using sharepoint folder as my data source. In which, there are 4 folders(Dev,QA,Test and Prod) and in each folder i have same Excel file(Example: ProjectData.xlsx). I have developed my report using Dev folder path. Now I would like to change the Source path as well as folder path dynamically using parameter. Even, that i've implemented. For example: One parameter for Source path like "Source_Link" and other parameter for Folder Path like "Folder_Link" and I provided the list of paths correspondingly.

Example: Source_Path---> https://educate.sharepoint.com/worthydata/

Folder_Path---->  https://educate.sharepoint.com/worthydata/workingarea/Dev/ProjectData.xlsx

In the above exmple you will find that, my Source_Path is there in Folder_Path till .....worthydata/. But, I don't want like that. It should show like "Souce_Path/workingarea/Dev/ProjectData.xlsx".

Is there any option to concatenate two variable in power bi?

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You could create a query parameter under Edit queries->Home->Manage parameter->New Parameter

Power BI Introduction: Working with Parameters in Power BI Desktop —Part 4

Then use this parameter ((file parameter) in the Source code.

Reference:

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

https://www.excelguru.ca/blog/2018/05/30/creating-dynamic-parameters-in-power-query/

As for use a Relative Path, I hope this thread will be helpful.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I created a parameter and used that like

= Excel.Workbook(File.Contents(Parameter1 & "\Sales Data Used in Video.xlsx"), null, true)

[Anonymous]

If you publish your pbix file, power bi service do not refresh, here the message This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

• Data source for Query1How do you fix this? please

[franciscocohen]

were you able to find a solution?

I'm having this dynamic source refresh issue in power bi service when I get data from SharePoint.Tables