Passing Parameters to Query loading from text file

[BlairosNZ]

Hi There

I have an environment set up where I load pre-set queries from a Text file.

They are brought in using a "LoadQuery" function (using the "Expression.Evaluate" function and #shared environment variable)

These queries utilize parameters set up in the main Power BI Desktop file.

In preview, these queries function perfectly, however, when it comes time to load these into the Power BI model, it throws an error...

To put this into context, the text-file query uses Parameters, namely "Root" for the drive source, then a folder, which is defined using another Parameter called "Region"

Example of this is:

Source = Folder.Files(Root & "\Pipeline Files\" & Region & "\Pipeline\" & Text.From(CurrentPipelineYear))

Root, Region are Text Parameters... and CurrentPipelineYear is a number.

Now, it doesnt seem to have any problems with the "Root" parameter, but its getting to the "Region" then throwing this error.

Like previously mentioned, this works fine (edit and load) in Excel, works fine in the Query editor for Power BI desktop, but will not load to the model as I need it to...

Any help to solve this would be greatly appreciated - i really want to be able to continue to use "single source of the truth" queries, as it means I only have to update one query, instead of the same query in every workbook/pbix i manage...

Thanks!

Blair

[v-danhe-msft]

Hi @BlairosNZ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @BlairosNZ,

Based on my research, you need to check the file path of your parameter and make sure the name is consistent with your folder. From the error description, the problem may occur that the "Region\Pipeline\" is not a correct file path.

It could work on my side.

Regards,

Daniel He

[BlairosNZ]

Hi there,

thanks for for the feedback, and apologies for the delay in response.

Unfortunately this did not resolve the issue, and after some more research it seems to be related more to the #shared environment not picking up the variables in question.

I will locate the article I referenced and post the link here, as it is quite complex, and as such I still am yet to resolve the issue in question.

Thanks

Blair