Create Power BI report and publish in SharePoint

Introduction:
In this article, we will learn how to create a report in Power BI using SharePoint List as a data source, using DAX (Data Analysis Expressions) in Power BI, publish the Power BI report to SharePoint Online, and Schedule Data Refresh. We will also learn how to use Power BI desktop tool which is used for designing reports in Power BI.
Articles in SharePoint Business Intelligence (BI) series: 🔗

Power BI in SharePoint Online
Creating reports using OData Feed in Power BI
Power View report in SharePoint
Display Power BI reports in SharePoint Online

Environment:
1. SharePoint Online - Office 365 (applicable to SP 2016/2013)
2. Power BI - needs Power BI Pro License
3. Power BI for desktop - for designing the report
Create report using Power BI Desktop from SharePoint List as Data Source:
1. Let's use the same data Finance Sample Workbook from Power BI site for this example as well
2. Load the data in SharePoint List
3. Open the Power BI for desktop tool and sign in using your O365 ID & password
4. Click File -> New -> It should open the blank report
5. Click Get Data option from Home menu -> More -> Online Services -> SharePoint Online List as shown below:

6. Enter the SharePoint URL - just the site collection but not till List

7. Once you enter, select Microsoft account option from Anonymous, Windows, Microsoft account options displayed for authentication as shown below:

8. Once authenticated & connected, it will display all lists and library in the site collection. Select the List (which we loaded the Finance Sample excel work book) and either click edit to edit the columns or click Load to load the data to the Power BI

9. Important point to note is that we have loaded the Finance Sample Excel Workbook in to SharePoint List and now the data is connected to Power BI. If you notice the loaded data (columns Gross Sales, Sales Price, COGS, Profit related to cost) in Power BI are loaded as text instead of $$$ value in this example

10. Using Power BI, we can change the data type (NOTE: 💡changing the data type will affect the data refresh so it is a good practice to change the data type in the source or load the data at source with appropriate data type). For this example, I have changed the data type in Power BI for desktop. 

Option is Data section -> Modeling tab in Ribbon -> Data Type & Format as shown below:

 11. Loaded data and updated with data type is shown below:

12. In the above data loaded, we see the month, year are in a separate column, let's use DAX expression to combine them and display as Year - Month in a separate column. 

I have added a new column "Date" as shown below:

13. Once the data is loaded, fields section shows the column that can be added to report, also the fields which can be displayed as aggregate/sum and also the measure which we created (Date field in above step):

14. Now, it's time to add the charts in the report section. I have used Card, KPI, Line & Clustered Column Chart, Pie Chart, Line Chart as shown below:

Publish the report to SharePoint Online (Power BI Pro)

15. Now, it's time to publish the report to SharePoint Online 😃 

First step is to publish the report to work space in Power BI site then to SP Online.

Log in to the Power BI site. In the above step, I have published the report to "My Workspace" section. Hence navigate to the My Workspace section in Power BI site -> Reports section (since this is published as report but not dashboard) -> click on the report ("Sales Report")

Now, click on the File -> click on select Embed in SharePoint Online preview as shown below. This will give the link to publish the Power BI report to SharePoint Online.

16. Now, time to integrate the report in SharePoint Online. In previous article, I have explained  about creating Communication Sites in SharePoint Online. In this site, create a page and add Power BI (preview) web part 

17. Now, name the page and publish the page. Below is the preview of the page and how the filter works in the chart according to the selection:

💡 Embed in SharePoint Online is only available with Power BI Pro. Each user that views the report mush have a Power BI Pro license. If they do not have a Pro license, they will be prompted to sign-up to view the report.

18. Next one final step is "Data Refresh". Navigate to the Power BI site again -> Workspaces -> My Workspace -> Datasets -> select your report (Sales Report) -> Schedule Refresh button in Actions section to schedule the data refresh as shown below:

Apple Power BI App preview of the report

Granting Access to reports:

Embedding a report in SharePoint Online does not automatically give users permission to view the report in SharePoint site. The permissions to view the report are set within Power BI service itself. 

There are two ways to provide access to the report within the Power BI service. 

1. If you are using Office 365 Group to build your SharePoint Online team site, you list the user as a member of the app work space within the Power BI service. This will make sure that users can view the contents of that group. For more information, see Create and distribute an app in Power BI. 🔗

2. Alternatively, you can grant users access to your report by doing the following.

Add a tile from the report to a dashboard.

Share the dashboard with the users that need access to the report. For more information, see Share a dashboard with colleagues and others. 🔗

Reference: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-embed-report-spo 🔗

Publish the report to SharePoint (without Power BI Pro option)

Suppose you don't have Power BI pro license but want to integrate the Power BI report in SharePoint 2013 or 2016 or in SharePoint Online, then Page Viewer is the option we have 😉

Navigate to the page where you want to display the Power BI report -> add a Page Viewer Web Part -> add the link copied from Power BI site (Report -> File -> Embed in SharePoint Online(Preview)-> copy the link)

 

Differences between publishing using Power BI pro & in Page Viewer web part

1. Show/Hide Navigation Page, Filter Pane options are available only in Power BI

2. Options to change the display size of the report is available only in Power BI pro  

3. Sorting the each section of report by it's main report parameter is available only in Power BI pro

4. Option to See Data of a particular report is available only in Power BI pro

5. All the above options are not available in normal publishing way

6. Share the report via Facebook, Twitter, Linked In, Email, & information about report, flagging report option is available in normal publihing method (Page Viewe web part)

Export to PowerPoint (Preview)

There is another option available in reports in Power BI site which is Export to PowerPoint(Preview). Navigate to Power BI Site -> Select the report -> File -> Export to PowerPoint (Preview) as shown below:

The exported file is attached here to know how a exported Power BI report looks like. See Downloads section for the file.

Downloads: 🔗

Power BI Desktop
Power BI for Mobile - Windows, App Store, Google Play
Power BI report file
Sales report exported to PowerPoint
So, we have learnt how to create a Power BI report from SharePoint List as data source, created DAX expressions, added different chart types, published the report to Power BI and then to SharePoint Online, SharePoint Online with out Power BI Pro and also applicable to SharePoint 2013 & 2016.

Please free to comment. Always. your comments help me to write more.😃 Share this post to others if this helps you!😇

Don't forget to subscribe to the posts to get notified about new tutorials by clicking the Subscribe button on the top right corner and follow us in social buttons as well.

Read More

Communication Sites in SharePoint Online

In this year SharePoint Virtual Summit, Microsoft announced several improvements to SharePoint Online and One Drive. One of the important and interesting additions is new “Communication Sites”

Microsoft says: “Reach your audience via SharePoint Communications Site” 😎

In this article, we will learn about what is new about it? How to create it and how does it work? Did we get something newly added to SharePoint Online? Are we really looking at something innovative?

What is a Communication Site?

Communication site is like Team Site in SharePoint site that anyone can create in the organization from SharePoint Home (it is available only in Office 365 and not in on premise environment). So, this is for internal users of organization.

Communication sites are beautiful, dynamic sites that let you reach a broad internal audience, and that appear great on the web, in the SharePoint mobile app, on PC and of course on Mac.

How to Create Communication Site?

From home page of SharePoint site collection -> click on the app launcher -> click SharePoint -> just click Create Site (if you don’t see this option then your self-service site creation is not enabled for your ID) as shown below:

-> 

If you didn’t get this option, you may have to change the settings in Admin Center -> Settings -> Organization Profile as shown below 😉

For more information of standard and first release options in Office 365, check out this information from Microsoft 🔗

Note: It took just 10 minutes for me to get the updates to my ID after enabling this option. Way to go Microsoft!! 💪Sometimes it may take few hours or overnight to update/assign new features to your ID
I have also noted how fast it creates these sites. It just took 15 seconds in our tenant which is pretty fast!

Communication Site Designs:

There are three types of design available in Communication Site:
1. Topic
2. Showcase 
3. Blank
First two types are pre-configured with some web parts by default and the blank one is to start from the scratch. 
Topic design explained
Use the Topic design option when you have a lot of information to share, such as news, events and further updates with good images which describe the information.

I have created a site collection using "Topic" design which is shown below:

Following are the list of web parts(some NEW) or the changes made to this site collection or you will observe in this page:

I have explained each web part/changes in detail below:

NEW Hero web part
This is a new web part which has tiles with various layouts available to show the highlights of the site. You can embed a image, link to the external content. I would say there is no need to create a custom news slider/carousel 😉 Just configure this web part!!

News web part 

This is existing web part where you can add images, link to the SharePoint pages with content and in side by side layout. I have added few images, content from Microsoft which is shown below:

NEW Events web part
This web part shows the upcoming events. It's not new, but it just the good old Calendar list but displays in a different view in the home page as shown below:

Quick links
This web part aggregates quick links to documents, pages or to external URLs. This web part is actually empty which is not visible upon site creation but will show up once customized as shown below:

Documents web part
An existing web part pre-configured to show 10 items of the current site’s document library, in a card-style layout.

NEW People web part 
This web part is empty when the page is added. You can add the people to contact or someone who is related to that page or team members, etc. This is linked to Delve profile.

Comments on Page

Each news article and page can have its own set of comments. So, commenting on page is possible now.

Disabling comments to the page option is also available. Comments can be switched OFF/ON with the below option shown below:

Easy Page Layouts (finally!!)

Now with communication sites, you can use a variety of multi-column section layouts on your pages, to arrange information side-by-side. Click on edit page to view the page authoring toolbox. It has new Section layout choices. 

I personally think this is one of the best features newly added. This will help to have multiple layouts on the same page and it is easy to create as well - Just in clicks. This will help the content editors/authors to create pages in SharePoint easily than before.

Top Navigation changes

New way to edit the site links, name and title from the page without navigating to other pages as shown below:

NEW “See all” pages

When there is more content than can be shown within the first view the Highlighted content and Site activity web parts, you can click See all to go to a full-page experience to see all the content and activity.
NEW Quick Chart
As the name suggests, this web part allows to add a chart quickly and represent your data in either bar or pie chart. Up to 12 data can be added which is allowable limit now. I am sure MS will add other chart types soon.
Also, this is not a web part where you connect the data, show real time results but this is a web part which allows the content authors or power users to visualize the data quickly.

NEW Bing Maps
With the Bing Maps web part, you can display a place / address and pin the location as shown below. This has Road, Street and Aerial views like normal Bing Maps.

NEW GIF support

When you add an Image web part into a news article or page, you can now include animated GIFs in your layout.

Power BI Integration

Bring in interactive reports using the Power BI web part, display reports and dashboards in a single click. 

Microsoft Stream

With the Stream web part, you can display a video from Microsoft Stream directly on your site. Screenshot at the end of Topic Design explanation 
NEW Document Library/ List Preview
 As the name suggests, both will display the preview of Document Library and List respectively. It is like List view web part but we have quick options to limit the items displayed. It has Auto Size, Small (5 items), Medium (15 items) and Large (30 items) display options. It also has "see all" option to view all items.

Yammer integration
It is now possible to use the Yammer web part for broad discussion scenarios as well as targeted responses to engage your viewers on the message and content on the page.

Share news via email
When you share news via email from a communication site, it’s not just a blue link but a visual, informative preview that adds context to both the email and the news article itself.
Within the email, the recipients will see a thumbnail, title, description and an optional message from the sender.

Set Focal Point
In the modern UI, a site page has a top image which was blank previously. Now, Microsoft has added a really nice touch to this page header; a way to add a image and also re position the focus on the image. In edit mode, you'll find a "Set focal point" button on the top right.

Consume, create and connect from your mobile device via the SharePoint apps

It’s easy to access, engage with and create content for communication sites from any device. The full site, pages, news, navigation, search and more are native view able, functional and engaging. Almost you can do everything using mobile app 😉

You can download the SharePoint mobiles apps for iOS and Android. Learn more about the SharePoint mobile app for iOS, SharePoint mobile app for Android and SharePoint app for Windows 10 Mobile. 🔗
I have also tried to create a page and add some of this web parts / features which looks as shown below(Some images, Videos embedded belongs to Microsoft):

Modern Page Editing
New page layouts available when you create a page:

Showcase Design 

Use the Showcase design option to feature a product, team or event using photos or images. This site is more simplistic. The home page contains only the new Hero web part and a classic Image gallery web part in the home page.

Its purpose is to hold information that is more "static". Like the description suggests, it's a perfect layout to present a product, a team or an event.

 

Blank Design Explained

Use a blank site and to piece together your own vision for a communication site to share information of your choosing. 
Tons of cool features!!! We will explore more in the forthcoming articles as well.
See Also

• Modern Team Sites in SharePoint Online Explained
• Differences between Communication Sites and Modern Team Sites

Please free to comment. Always. your comments help me to write more.😃 Share this post to others if this helps you!😇

Don't forget to subscribe to the posts to get notified about new tutorials by clicking the Subscribe button on the top right corner and follow us in social buttons as well.

Read More

Power View report in SharePoint

Introduction:
Welcome to Business Intelligence Article series, readers 😉 Yes, we are going to learn in detail about Business Intelligence (BI), Creating Reports, Data Refresh, Integrating the BI reports with SharePoint, etc. in the forthcoming articles.
For every business function automation or where you have large data stored - BI reports play a vital role in analyzing the existing data, improving from the past experience and predicting the future of business, etc.
In this article, we will learn how to create Power View reports from SharePoint list and integrate with SharePoint in detail.
So, make sure you subscribe (look at the options at right top corner) to get the latest articles in your email or notified about the latest articles!
Articles in SharePoint Business Intelligence (BI) series: 🔗

Power BI in SharePoint Online
Creating reports using OData Feed in Power BI
Power View report in SharePoint
Create Power BI report and publish in SharePoint

Environment:
SharePoint Online - Office 365 (should work in SharePoint 2016 & 2013 versions as well)
Excel 2016 - Microsoft Office 365 ProPlus (should work for Excel 2013 versions as well)
Why Power View?
If you have already migrated or migrating to SharePoint Online and want to create reports/dashboards in SharePoint then following are the options available:

1. Using Excel Services/Excel Web Part

2. Power BI

3. Using third party solutions

4. Custom solutions using JQuery chart plugins

💡If you look at the above options, using Excel Services and Power BI are the easiest ways one can create reports in SharePoint. 3rd and 4th options need in depth knowledge in customization to build reports. But for using Power BI you need to buy a separate license. Obviously, we will go for the Excel Services/Excel Web Part option which comes with MS Office😎

So what BI features are available in SharePoint 2013/2016 (on-premises) but not available in SharePoint Online?🤔 
1. Performance Point Services (PPS dashboards)
2. Scorecards and Dashboards
3. SQL Server Reporting Services (SSRS) in SharePoint Integrated mode
For more information of what is not supported in SharePoint Online in Office 365, refer this article.🔗
Creating Power View reports in SharePoint:
1. For this demo, I have created a SharePoint List named "Finance Sample Data" using the data from Finance Sample records downloaded from Power BI site. Click to download. 🔗 It has around 700 records which is very less when we compare the data load which is 1 Million records.😮
2. Let's go ahead connect the data from SharePoint to Excel and create excel dashboards. If you are using Excel 2016 and don't see the Power View option in the ribbon, read this article to enable it.🔗
3. Open Excel -> new workbook -> Data -> New Query -> From Online Services -> From SharePoint Online List -> Enter the SharePoint Site URL -> Select the list "Finance Sample Data" from the list -> Click on Edit button at the bottom right corner to edit or you can load all columns as per your requirement -> You can choose columns or remove unwanted columns -> Load the data to Excel

4. Once the data is loaded it will open the blank Power View report. Now, it is time to add the report. 💡Remember Power View is a Silverlight based application and Power BI is HTML based application. Power View cannot be customized within the report. But Power BI is customizable. You can click any columns at the right side menu and add filters.

5. In the same way add data by dragging the fields. I have added the 4 report sections as shown below. The final report created in Power View is:

6. Also, when you click on the chart area, it will filter according to the data relationship and filter the data automatically 😃

7. Now, its time to publish the report to SharePoint so that we can display the report in a separate page. Very important, if you don't follow the below step you will get an error when linking the report in excel access web part. 

Instead of uploading the report to Document Library -> click on Save As -> Connect to the SharePoint Online site (if not connected already) -> then click on the site to view the libraries -> publish to the desired library

8. Add a new SharePoint page -> Insert Excel Web Access web part -> Add the link to the excel sheet published in SharePoint library -> Save it to view the Power View report in SharePoint.

9. Final report published in the SharePoint looks like below:

10. Next what? We forgot about the Data Refresh option. In the excel sheet -> click on the Data tab -> Connections -> Properties -> Usage as shown below:

As usual, I have shared the List used (List Template with content), Excel report created. Click here to download.

Please free to comment. Always. your comments help me to write more.😃 Share this post to others if this helps you!😇

Don't forget to subscribe to the posts to get notified about new tutorials by clicking the Subscribe button on the top right corner and follow us in social buttons as well.

Read More