Let's say you use an Excel workbook as your data source in Power BI. Than you collaborate with a team and as we know, not everyone has the same folder structure setup. This can cause a problem when trying to Refresh data in Power BI. How can we "overcome" this in a simple way?
Just create a Power BI parameter and pass it to the Power Query responsible for opening the Excel workbook.
In the following printscreen you see that I created a parameter called Excel workbook path. I set this path to where the workbook is located in my machine.
Then we can use this parameter in Power Query like this:
= Excel.Workbook(File.Contents(#"Excel workbook path"&"Contingency Drawdown.xlsx"), null, true)
I was having some difficulty trying to make the string concatenation work, that is, joining the parameter with the workbook name. Tried some variants and got it working with the string concatenation token &.
As you see the workbook name doesn't change (it's hardcoded) however the path is customizable by the parameter "Excel workbook path".
We just need to change this parameter value to point to our folder structure and we're done. Now we can make use of this parameter to open all Excel sheets (tables in Power BI) in a single shot.
Hope it helps.
References:
Power BI Introduction: Working with Parameters in Power BI Desktop —Part 4
