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:

ExcelTableName

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:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sample-uri-for-excel-services-rest-api

For PowerBI, Odata maybe the best one to selected

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/using-odata-with-excel-services-rest-in-sharepoint

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

DataFromWeb

2. Sign in by your organization account

SignIn

3. Make sure you have data in Power Query Editor

PowerQueryEditor

Like this

QueryResult

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”

AdminCenter-ExternalSharing

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”

AdminCenter-SiteCollectionSharing.png

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

ClickShare

Then copy the link in the dialog.

So, the link should be similar this:

https://yoursite.sharepoint.com/:x:/g/publishing-report-center/publishing-sale-reports/ETflhztYjGdCljjlf-0nt8kBICp–iVrpej5m2b–sGmQA?e=7r7fRe

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

TakeShareOption

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:

https://yoursite.sharepoint.com/:x:/g/publishing-report-center/publishing-sale-reports/ETflhztYjGdCljjlf-0nt8kBICp–iVrpej5m2b–sGmQA?e=7r7fRe&sourcedoc={3b87e537-8c58-4267-9638-e57fed27b7c9}&action=embedview&ActiveCell=’OverviewTable’!B4&Item=TableOverview&wdHideGridlines=True&wdInConfigurator=True

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

ReportLocation

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)

EmbedMenu

Select the correct “What to show”

WhatToShow.png

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

YourReport

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

Select_columns

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

AdjustFieldsToAreas.png

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:

Accummulate_monthly.png

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

Monthly.png

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:

Percentage_Accummulate_Monthly.png

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:

Table_Total_Accummulate_monthly

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:

Chart1.png

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…”

FormatDataSeries.png

Select it like a secondary axis

SecondaryAxis.png

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

MoveLegend.png

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

SelectDatabaseAndTable

4. Save data connection file and finish

SaveDataConnectionFile.png

5. Update connection properties

ImportData

UpdateConnectionProperties

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

SelectColumnsAndPutToAreas.png

7. Remove subtotals on the field settings

NoSubTotal

LayoutAndPrint

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

VesselLoadListing.png

 

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;
password=aaaa;
MultipleActiveResultSets=True;
App=EntityFramework”

use ngodemo

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

select * from Sample_Customers

 

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh
End Sub

Sub updatepivottables()
Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh
ActiveWorkbook.Save

End Sub