cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav
Continued Contributor
Continued Contributor

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

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.

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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
Community Support
Community Support

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors