Referencing a column value as file path to get Excel data file in Power Query

[anandav]

Hi,

I can give a network file path using Poawer BI parameters and use the parameter to get the Excel data file.

Source = Excel.Workbook(File.Contents(Parameter1), null, true)

I can save this report to the PBI Report Server (in-house).

Then I changed the path to be the value from a table column and in PBI Desktop it works fine.

Source = Excel.Workbook(File.Contents(QueryName{0}[ColumnName]), null, true)

But when I try to save (publish) this to the PBI Report Server (October 2020 version) I get an error message "An error occured when trying to save to Report Server..."

Can anyone help me with how the give the data file path stored in a table?

Thanks in advance.

[v-jingzhang]

Hi @anandav 

Does my reply solve your problem? If yes, kindly accept it as the solution. If you find other solutions, you can also share them here to help other users in the future. Thanks.

Regards,
Jing

[v-jingzhang]

Hi @anandav 

You may try the following steps:

1. Right click on the column which stores the data file path and select Add as New Query. This will generate the column into a new list. I call this list FilePath.

2. Create a parameter Parameter1, set Type > Text, Suggested Values > Query, Query > FilePath.

3. Modify the Source by using the parameter: Source = Excel.Workbook(File.Contents(Parameter1), null, true)

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.