Why Power BI?

Although my background is all about SharePoint, collaboration, intranets, DMS…etc., but recently I have been speaking in different events about Power BI. It is a new member in the Office 365 family that enables users to do self-service BI.

It fascinated me how easy it is to get a grasp of this tool, if you have basic IT skills. It requires someone understands the concepts of data, tables, relations, queries, and can use excel features.

If you are more experienced Data Analyst (sometimes called Business analyst, Data steward or an Excel Guru!), you can easily perform advanced calculations and analysis in Power BI.

I decided 6 months ago to learn more about Power BI and how it can help the business. I have gone through good tutorials available by Microsoft. I highly recommend you to go through them if you want to explore the basics of Power BI.

1. Import Data into Excel 2013, and Create a Data Model

2. Extend Data Model relationships using Excel 2013, Power Pivot, and DAX

3. Create Map-based Power View Reports

4. Incorporate Internet Data, and Set Power View Report Defaults

5. Create Amazing Power View Reports – Part 1

6. Create Amazing Power View Reports – Part 2


1. Add Data to Your PowerPivot Workbook (Tutorial)

2. Save Your PowerPivot Workbook (Tutorial)

3. Create Relationships Between Tables (Tutorial)

4. Create a Calculated Column (Tutorial)

5. Create a Hierarchy in a Table (Tutorial)

6. Create a PivotTable from PowerPivot Data (Tutorial)

7. Create a PivotChart from PowerPivot Data (Tutorial)

8. Add Slicers to PivotTables (Tutorial) , and Add Slicers to PivotCharts (Tutorial)

9. Create a Measure and KPI (Tutorial)

10. Create a Perspective (Tutorial)

However when I started reading about Power BI, I was confused. All the tutorials are using add-ons (I will discuss later) in Microsoft Excel, I couldn’t understand what is the relation between the excel file I created and Office 365.

Power BI offering consists of different components that complements each other. Some of these components are desktop components and others are online in Office 365.

Desktop components – They are excel add-ons.

Most probably this will eventually change with the announcement of the Power BI Designer (I will post a separate blog post about it)

1. Power Query – allows you to query data from various data sources that includes files, SharePoint Lists, SQL Server database, SSAS, Azure table storage, HDInsight…etc. See list of all connectors here. In addition to querying data, Power Query allows you to manipulate the data to reformat the data structure for example pivot columns, transform data types, add or remove columns…etc. All the transformation steps are recorded so when the data refreshed, all the steps are applied, and you don’t have to redo the steps. The data queried either stored in an Excel sheet or Data model in Power Pivot. Power Query uses the Power Query Formula Language (informally known as “M”) to execute all the transformations you configure. You can write M Language directly to achieve more advanced scenarios.

2. Power Pivot – Creates data model based on the data queried inside Excel. Power Pivot uses a technology that can compress large size data into small excel files. In Power Pivot, you can add calculated columns, define KPIs and new calculations using DAX.

3. Power View – an interactive data visualisation and exploration tool. Users can use different of charts including maps. Users can define slicers and filters to the reports. Excel sheets published to SharePoint can display Power View report in the browser using Silverlight or HTML 5.

4. Power Mapdata visualization tool to display data on maps. Maps can 3D or 2D. Users can define scenes that tell a story using the data and charts on the map. Data can be visualized on the map using Stacked Column, Clustered Column, Bubble, Heat Map, or Region chart.

Online components

1. Power BI subscription: for the users to be able to share queries across the organisation or share Excel sheets, they need Power BI subscription. The Power BI licence is different from the Office 365 subscription; you will need to buy a separate licence. Power BI provides the users with Power BI portal, allows data refresh from on-premise data sources using the data management gateway.

2. Power BI sites – a central site allows the users to share and find Excel reports. Using Power BI users can view Excel sheets up to 250 MB (the standard is 10 MB).