Introduction to Power BI for Office 365 devsChris O’Brien - MVP

A quick guide to getting started with Power BI….

…from someone else who isn’t an expert either

Why consider Power BI over custom dev?You want easy visualisations (charts,

maps etc.)

You want easy sorting/filtering

You have drilldown or parent/child scenarios

You want to support mobile

Your client has E5 or Power BI Pro licensesYour data is in SQL, SP lists, or a file (e.g. Excel, XML etc.)

Easier than with D3 for example..

Getting startedMake sure you get the right bitsPower BI *Desktop* app to build dashboards – NOT Power BI app in Windows store

- https://powerbi.microsoft.com/en-us/desktop

Consider licensing, even for your “dev environment”You either need:

- E5..or..

- An individual Power BI Pro license to be added to the tenant

Power BI Desktop – what it looks like

Connecting to dataEasy optionsAzure SQL DatabaseSharePoint listsStatic file – Excel/CSV/XML/JSON etc.OData feed

Bit more involvedOn-premises SQL – need to install “gateway” component on server in network

How is the data referenced?1. Generally “brought into model”Periodic refresh if live data underneathRefresh button

2. Direct QueryCan connect “live” to:

- Azure SQL- on-prem database

Working with the data..Tables and columns now detected

Can use “Edit Queries” button to bring in extra columns/change detected relationships etc.This brings up Query Editor

Key process1. Use “New Visual”

button to add a shape

2. Select the typePie chart, donut chart etc.

3. Bind it to dataField in the “Values” section needs to be something which identifies the row e.g. the primary key

Result:“Jobs by zone”Next steps:

- Rename label- Decide if this should be a filter

(for other controls)

Working with filters

Drag fields to the “Filters” section of the control to make it act as a filter

More on filtersUse the Edit Interactions button to control which other visualizations should react

“Filter” or “do nothing” buttons will appear on other filterable visualizations:

Common patternUse filter visualizations with Table visualizationSelect item in chart –> filters items in table

SlicerThe Slicer is a special kind of visualization – it acts as a filter depending on data type:

Int: Date/time:

GotchasCannot select item in (the standard) table This would be useful to drill down to a “Details” view of item

Slicers can be inflexible Lack of image support

CUSTOM VISUALS TO THE RESCUE!

Recommended custom visualsInvestigate to see if they meet your requirements

Original Microsoft control

Custom controls

Table Grid (by MAQ Software)Slicer Chiclet slicer

Attribute slicerTimeline slicer

Obtain from https://store.office.com/

Publishing

PublishingTypically will publish to an Office 365 Group..or use “My Workspace” when in dev/testing..

Other publishing optionsSPFX web partStill requires E5/Power BI Pro license for users!

User credentials are used to connect to report (i.e. user must have perms to page + report)..

..but connection to underlying data can be other creds..

Other publishing optionsPublish to webDone in web client – gives you a anonymous URL and an embed link

Suitable only if the data can be accessible anonymously!

Developing for Power BI – main optionsPull tiles/dashboards/reports into custom app

JS “embed report” option (but now also have SPFx web part)

Develop custom visuals

See https://powerbi.microsoft.com/en-us/developers/

Thank you!Any questions?

www.sharepointnutsandbolts.com@ChrisO_Brien