Excel REST API in SharePoint Online to PowerBI

Let’s says that you have an excel file located in a SharePoint library, and you know where to get data like this, and you want to display them in Power BI:


Yes, PowerBI can get data from an excel file, but how can we do that for excel online? Luckily, there are some example URL from excel services that we can use to fetch data from Excel:


For PowerBI, Odata maybe the best one to selected


1. Get data from web (past odata link to the URL)


2. Sign in by your organization account


3. Make sure you have data in Power Query Editor


Like this


SharePoint online: Share your excel report to anonymous users

Sometime, you have an excel report located in your SharePoint Online, and you want to share it to anonymous user (not require to login), how can you do that?

First, you need to turn on “Allow sharing to authenticated external user and using anonymous access link”


Then, select the site collections that you want to share to anonymous, turn on this “Allow shareing with all external users, and by using anonymous access links”


Now, we go to the excel report that you want to share to external user and click share.


Then copy the link in the dialog.

So, the link should be similar this:


But wait, you don’t want to share the whole file, you just want to share the report in the file. Humm, let’s get the report link first:

In your excel online file, click to File -> Share -> Embed


Select the report area in the embed dialog, then you will have a script similar this:

< iframe width=”1024″ height=”1024″ frameborder=”0″ scrolling=”no” src=”https://yoursite.sharepoint.com/publishing-report-center/publishing-sale-reports/_layouts/15/Doc.aspx?sourcedoc={3b87e537-8c58-4267-9638-e57fed27b7c9}&action=embedview&ActiveCell=’OverviewTable’!B4&Item=TableOverview&wdHideGridlines=True&wdInConfigurator=True“></ iframe>

Now, you can combine 2 links above to have a final link for your report. The final link should be like this:


Use the tool online to make that url shorter, then from now on, you can send that link to anonymous user to see your report.

Good luck!

Display Excel chart in SharePoint online

1. Select location for your excel report, by this way, you can manage the report permission as the other documents


2. Access to the sheet, where you have the report. On the right hand, click on “Share” -> “Embed”   (Or you can edit the workbook online, and click into File->Share)


Select the correct “What to show”


3. Adjust the “Appearance”, “Interaction”, “Dimensions” and copy the “Embed code”

4. Go to the page where you want to display the report. Add a content webpart with the embed code above


Good luck!!!


% Accummulate YTD on Excel Pivot Report

1. In the excel ribbon, select Data -> From Other Sources -> Select a fact table and all relation tables -> Finish the data connection wizard

2. Select “PivotTable Report” in “Import Data” dialog -> Click on “Properties”

3. Select “Refresh data when opening the file” and go to the definition tab -> checked on the “Save Password” and select “None” on “Authentication Settings…”

4. Select all the fields you need for the report


5. As I see on the image above, the selected fields are not in the correct area. I need to adjust them like this:


6. Yeah, look at the table, I have the total amount on each service activity in each separate month.

7. Rename the existing “Sum of Total Amount USD” to “Monthly”.

8. Drag the “Total Amount USD” to the value column for the accummulate. Then, update the “Value field settings” to make sure we have the right accummulate:


Now, I have Monthly value and Accummulate monthly on each month for each service activity


On the right, I have something called “Total Monthly”, mean “Yearly” in my report. I also have “Total Accummulate monthly” for the year.

What I need now is the percentage for the year as well for my report 🙂

9. Continue drag the “Total Amount USD” to “VALUES” area, change the name to “% Accummulate Monthly” and show value like this:


10. Perfect, now I have everything as the datasource for my report.

11. In the report, I want to show something like this:

12. So, I need to remove some of columns that not necessary for my report, in this case, they are “Month” and “Accumulate Monthly” (Or I should remove them at the beginning ?). Anyway, now I have the table like this:


13. Enter my mouse to inside the table, in the ribbon, click “Insert”  -> PivotChart -> “Line” -> “Line with Markers”

Yeah, now I have a very nice chart like this:


14. But, wait, the percentage like seem not correct? Since we are missing the data series for it. Right click on the red line and select “Format Data Series…”


Select it like a secondary axis


Make my chart bigger and remove some buttons, move the legend to bottom.


Then I good to go 🙂

Working with Excel Pivot Table

1. Open the excel file

2. On the ribbon, select “Data” -> “From Other Sources” -> “Connect to database server”

3. Select Database and Table


4. Save data connection file and finish


5. Update connection properties



6. Select pivot table fields and put them to correct areas


7. Remove subtotals on the field settings



8. Yeah, now you have a listing like this:



PivotChart using external datasource and display on SharePoint Online

Today, I’ve received a requirement like this:

“In Excel, display a chart, the data for that chart is getting from Azure database. Finally, display the chart on the SharePoint page. The chart should be updated when data is updated the Azure database.”

Easy, right?

data source=my.database.windows.net,1433;
initial catalog=my;
persist security info=True;
user id=my@my;

use ngodemo

update Sample_Customers set Country = ‘Canada1234’ where Country = ‘Canada123’

select * from Sample_Customers


Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Sub updatepivottables()

End Sub