This Power BI tutorial explains, how to generate a report through Power BI in SharePoint Online Office 365.
If you are new to SharePoint Online modern experience, you can follow the below tutorials:
- SharePoint Modern Experience – All You Need to Know!
- SharePoint Online Switch from classic experience to modern experience (List & Document Libraries)
- View in File Explorer is missing in SharePoint Online Modern document library
- How to set a modern home page in classic SharePoint site in SharePoint Online Office 365?
- Hero Web Part in SharePoint Online Modern Site
Integrate Power BI Reports in SharePoint Online
Please follow the below steps to generate a report in Power BI in Office 365.
Step 1: Log in to the SharePoint site -> Go to site contents -> Create 4 product details list as same as below screen shot.
Product List Here I have created 3 columns.
- Title -> Single line text
- Product Key -> Number
- Product Name -> Single Line text
Sales: This SharePoint Online list contains:
- Title(Single Line text)
- Sales Key (Number)
- Sales Region (Choice)
- Sales Country (Choice)
Sales Target: This SharePoint list contains:
- Title (Single Line text)
- Sales Month (Single Line text)
- Target (Integer)
Product Sales: This SharePoint list contains:
- Title(Single Line text)
- Product Key(LookUp) – look up to Product List
- Sales Order Number(Text)
- Unit Price(Number)
- Extended Amount(Number)
- Sales Key(LookUp) – look up to Sales list Sales Key
- Sales Month(Choice)
Step 2: Once the data is properly populated, we can go ahead and start designing our report. you should have a proper license assigned to your account before you can design, publish & share a Power BI report. you can find licensing details here.
Download Power BI Desktop here.
- Launch Power BI Desktop –> Get Data –> More
Step 3: Click on Online Services-> SharePoint Online List->Click on connect button
Step 4: Once you click on connect, It will ask you to put the SharePoint URL where you get the data.
Step 5: Once your site gets connected, all SharePoint list will populate over here and you will check the lists which you want. Next click on Edit button to change the dataType of the column.
Step 6: Next change type of column ExtendedAmount & UnitPrice to Decimal Number & select apply.
Step 7: Next click on close & Apply button on top of the link.
Step 8: It will take some time to load the query.
Step 9: Next Click on Manage Relationships from top navigation. we will delete existing relationships that Power BI has detected based on columns identical name and define our own relationship.
Step 10: Next it will populate 3 tables need to change the relationship. Just click on the table Name so that it will ask you modify the relationship.
Step 11: Here relationship should be ProductKeyId in product sales and productKey in the Product list table.
Step 12: Next ProductKeyID in Product sales and SalesKey in Sales table.
Step 13: Next Id:1 in Product Sales table and UD:1 in the Sales table.
Step 14: The manage relationships should look like below image.
Step 15: Select New Visual from top ribbon menu –> select this newly added visual on the screen and change it to pie-chart from right menu under Visualizations.
Step 16: You can select graphs, Pai chart, and graphs based o your requirement.
Step 17: Next click on the Publish button and save your PowerBi in your local folder.
Step 18: Now it successfully saved in your local drive or under workspaces in the cloud.
Step 19: Next open your workspaces and open the report section.
Step 20: Next click on publish to web to generate the Embed code.
Step 21: Now click here to generate the Embed code.
Step 22: Now you got the code so copy and paste it in SharePoint site.
Step 23: This is the final Dashboard report after embedding this code in SharePoint modern page.
- Integration of Power BI with SharePoint Online
- The SSRS service application proxy is not configured. Use the SSRS service application pages to configure the proxy
- Reporting Server Integration feature missing in SharePoint 2013
- Data Refreshing issue Access Denied to Analysis services Database. Contact Administrator SharePoint Business Intelligence (BI) services
- Provider Hosted App to display Power BI reports in SharePoint Online
- Data Refreshing issue Access Denied to Analysis Services Database Contact Administrator in PowerPivot in SharePoint BI services
- How to combine multiple files from SharePoint folder in Power BI
- Power BI: How to Connect Most Recent File from a Folder
This Power BI tutorial explains, how to integrate power bi with sharepoint online office 365.
Rajkiran is currently working as a SharePoint Consultant in India . Rajkiran having 7+ years of experience in Microsoft Technologies such as SharePoint 2019/2016/2013/2010, MOSS 2007,WSS 3.0, Migration, Asp.Net, C#.Net, Sql Server, Ajax, jQuery etc.He is C#Corner MVP (2 Times).