Forum Discussion
create parameter from concatenation
I have a report that sources from several Excel files under a SharePoint site. I'm trying to create a report-level parameter for each file to make maintenance of the sourcing easier (say, if the files or site get moved). Because they're all under the same SharePoint site, I'd like to create a parameter for the site URL and parameters for each Excel file. That way, for each Excel file, I can just concatenate the site parameter to some string, e.g., concatenate 'site parameter' with '\some_file_name.xlsx'. Is it possible to create parameters this way?
It seems that it's not possible to create parameters from other parameters (unless you query parameters, which is not applicable in my scenario). The solution is...
1) create the following 3 parameters:
parameter A = 'some super long path'
parameter B = \Excel_file1.xlsx'
parameter C = '\Excel_file2.xlsx'
2) and then concatenate the parameters at the query level, e.g.:
let
FullFilePath = #"parameter A" & #"parameter B",
Source = Excel.Workbook(File.Contents(FullFilePath ), null, true),...
5 Replies
mr_wizard , You can use M parameter and use it in the creation of a new column https://learn.microsoft.com/en-us/power-query/power-query-query-parameters
= [Paramater] & [File Name]
Or you can have static ur and create a column in power query or dax
"URL" & [File Name]
amitchandak Are you saying that it's not possible to create parameters from other parameters? In other words, for my scenario, I have to materialize my list of Excel workbooks as a single-column table by appending my parameter with strings?
mr_wizard , Are you looking to change the source string only? I was thinking of creating links for different files. see if this can help
refer, video from Guyinacube - How connection is using parameter
https://www.youtube.com/watch?v=XIq5vN5oPf8
https://www.youtube.com/watch?v=OnaDJkGOmIE
Infact you can use it in url
source =Parameter + "abc.Xls"
Also, you can create a list
https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters
