cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Josh_BI_UK
Helper II
Helper II

How to Access Excel or CSV Files Saved on Power BI Report Server Directly?

Hi PWR BI Family ❤ ,

 

I saw this post on how to access files saved on Power BI Report Server; however, I couldn't follow the solution. After a bit of digging around, I managed to figure out the intermediate steps you need to follow. This post is meant to enhance the original solution - not replace or criticise it:

 

Original post from @liewgm : https://community.powerbi.com/t5/Report-Server/How-to-Access-Files-Uploaded-to-Report-Server-Directl...

 

Hi,

I have PBI Report Server set up and running fine. Question is how do I access the files uploaded to the Report Server directly?

E.g. Pass an URL of an uploaded Excel (http://myServer/abc.xlsx) to Excel application to open it, make edits on the file and then save it back to PBI Report Server?

Thanks.


and the original solution provided by: @jcollinson2001 wrote: 

 

The file is stored as a binary object in the Report Server database. The Report Server API will allow you to open the file directly within Excel but not save it back. To open the file, you'll need to find the GUID of your file, and put it into the following API call:

http://<your report server>/reports/api/v2.0/Resources(<your excel file's guid>)/Content/$value

Then open Excel and paste that URL into the File->Open window.

You will have to manually save and re-upload the file to store any changes.

1 ACCEPTED SOLUTION
Josh_BI_UK
Helper II
Helper II

1a.) Navigate to your Power BI report Server find your Excel flat file and click the 3 ellipse dots to access the “Manage” menu.

 

1b.) Look for the bread crumb path near the top of your menu bar and right click your excel file name and then “Copy shortcut”. This is the URL you need which contains the files GUID.

 

E.g. of a totally fictitious server URL, the files GUID is highlighted in bluehttps://yourserver.0007.co.uk/PBIReports/api/v2.0/Resources(123abc456-your-files-GUID-here-789efg00)/Content/$value

 

 

steps_excel_report-server_01.png

steps_excel_report-server_02.png

2)    Open Power BI and select [Get Data], from main screen or “Home” menu on the Power BI desktop.

 

3)    Choose [Web] as a query data source.

steps_excel_report-server_03.png

 

4)    Enter the files URL, using the relevant API string: http://<your report server>/reports/api/v2.0/Resources( <your excel file's guid> )/Content/$value

 

steps_excel_report-server_04.png

 

5a. or b.) Once the query loads your source file, you should see a plain file icon, labelled with the same name as your report server. Notice that it loads the file as a binary file.

 

steps_excel_report-server_05.pngsteps_excel_report-server_06.png

 

Right-click the icon (or click the [Open As] folder icon located on the left menu, just underneath the main Power BI commend bar). Both methods do the same thing, i.e. open a new menu bar with suggested “open as” file types. Select “Excel”, or whatever your file type is.

 

6 or 7)  The next screen will load a table containing a list of any datasets held within your excel file.

 

steps_excel_report-server_07.jpg

 

In my example, I had data saved as an Excel table (labelled 6. on my screenshots). Power BI will also load any worksheets containing data, e.g 7 on my screen shots below. Click the dark yellow [Table] link

Expand TableExpand Tableto open up your underlying excel data.

 

 

 

That’s it, wham, your excel file held on the Power BI Report Server should load.

 

Here is the M Script if you find it easier and can adjust the code to fit your file paths:

 

let
Source = Excel.Workbook(Web.Contents("https://yourserver.0007.co.uk/PBIReports/api/v2.0/Resources(123abc456-your-files-GUID-here-789efg00)/Content/$value"), null, true),

My_Sample_Data_Sheet = Source{[Item="My_Sample_Data",Kind="Sheet"]}[Data]

in
Source

 

View solution in original post

4 REPLIES 4
id013
Helper V
Helper V

As a followup, if I'm using the excel file as a datasource using the rest API, is there a way to edit the excel file while it's residing on the power bi report server folder? Otherwise if I download it, make changes to it then re-upload it won't it have a different GUID? Which means I'll have to update the dashboard with the new GUID right? I'm basically looking for a way where the excel source can be updated and upon refresh the visual will use the updated data? Is this possible? 

Josh_BI_UK
Helper II
Helper II

1a.) Navigate to your Power BI report Server find your Excel flat file and click the 3 ellipse dots to access the “Manage” menu.

 

1b.) Look for the bread crumb path near the top of your menu bar and right click your excel file name and then “Copy shortcut”. This is the URL you need which contains the files GUID.

 

E.g. of a totally fictitious server URL, the files GUID is highlighted in bluehttps://yourserver.0007.co.uk/PBIReports/api/v2.0/Resources(123abc456-your-files-GUID-here-789efg00)/Content/$value

 

 

steps_excel_report-server_01.png

steps_excel_report-server_02.png

2)    Open Power BI and select [Get Data], from main screen or “Home” menu on the Power BI desktop.

 

3)    Choose [Web] as a query data source.

steps_excel_report-server_03.png

 

4)    Enter the files URL, using the relevant API string: http://<your report server>/reports/api/v2.0/Resources( <your excel file's guid> )/Content/$value

 

steps_excel_report-server_04.png

 

5a. or b.) Once the query loads your source file, you should see a plain file icon, labelled with the same name as your report server. Notice that it loads the file as a binary file.

 

steps_excel_report-server_05.pngsteps_excel_report-server_06.png

 

Right-click the icon (or click the [Open As] folder icon located on the left menu, just underneath the main Power BI commend bar). Both methods do the same thing, i.e. open a new menu bar with suggested “open as” file types. Select “Excel”, or whatever your file type is.

 

6 or 7)  The next screen will load a table containing a list of any datasets held within your excel file.

 

steps_excel_report-server_07.jpg

 

In my example, I had data saved as an Excel table (labelled 6. on my screenshots). Power BI will also load any worksheets containing data, e.g 7 on my screen shots below. Click the dark yellow [Table] link

Expand TableExpand Tableto open up your underlying excel data.

 

 

 

That’s it, wham, your excel file held on the Power BI Report Server should load.

 

Here is the M Script if you find it easier and can adjust the code to fit your file paths:

 

let
Source = Excel.Workbook(Web.Contents("https://yourserver.0007.co.uk/PBIReports/api/v2.0/Resources(123abc456-your-files-GUID-here-789efg00)/Content/$value"), null, true),

My_Sample_Data_Sheet = Source{[Item="My_Sample_Data",Kind="Sheet"]}[Data]

in
Source

 

View solution in original post

Hi,

I'm trying to do the same thing. I've uploaded an excel document to serve as a datasource but I can't get the GUID. How were you able to get it? I followed the steps you outlined and I don't see the guid id. 

Hi nevermind I was able to find it using the CataglogItems Properties switch. Thanks though.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!