cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Josh_BI_UK Regular Visitor
Regular Visitor

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

Accepted Solutions
Josh_BI_UK Regular Visitor
Regular Visitor

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

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

steps_excel_report-server_table_snip.pngExpand 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

 

1 REPLY 1
Josh_BI_UK Regular Visitor
Regular Visitor

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

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

steps_excel_report-server_table_snip.pngExpand 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