CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
skyline01
skyline01

Reputation: 2101

how to re-use a string across parameters in Power BI

I am creating a report in Power BI Desktop. Let's say that I have the following two Excel sources for a report:

  1. 'some super long path'\Excel_file1.xlsx

  2. 'some super long path'\Excel_file2.xlsx

As usual (to make report maintenance easier), I create two parameters (one for each source). But, since 'some super long path' is the same path in both sources / parameters, I'd like to make that path / string re-useable as well, so that I don't have to re-type it for both sources / parameters. (For two sources, this isn't a big problem. But, my actual report requires about 20 sources.) Is there a way to make this string re-useable? My idea is something like:

parameter A = 'some super long path'

parameter B = parameter A + '\Excel_file1.xlsx'

parameter C = parameter A + '\Excel_file2.xlsx'

But, that doesn't seem to be permissible in Power BI Desktop. As far as I know, the "list" solution (create parameter for 'some super long path', create list holding the Excel workbook names, create parameter that queries the list) won't work for me, as it requires a current value (a value that must be used by both sources), which defeats the purpose.

Upvotes: 1

Views: 131

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

You can simply use three different parameters

parameter RootDir = 'some super long path'
parameter B = 'Excel_file1.xlsx'
parameter C = 'Excel_file2.xlsx'

Then in queries you can concatenate them to create your file paths.

let
    FileName = RootDir & "\" & B,
    Source = ...
in
    Data

Upvotes: 1

davidebacci
davidebacci

Reputation: 30219

Create a new blank query and place the constant in there.

e.g.

let
    Source = "some super long path"
in
    Source

Then reference the constant concatenated with the parameter when you need the full file path.

Upvotes: 1

Related Questions