A Tour of Power BI

In this lesson we’re going to do one simple thing; we’re going to have a tour round the Power BI desktop software. We’re going to do this, firstly so that you can see that PowerQuery in Power BI is almost identical to what we’ve looked at so far in Excel and secondly, if you want to, you can start having a look at this excellent tool.

Downloading Power BI Desktop

For report building, Power BI has two key components:

1. Power BI desktop – this is a piece of software that you download to your computer and create PBIX files (Power BI report files). In Power BI desktop you create reports by:

• Collating information from data sources using PowerQuery
• Loading the data source information to the data model and creating custom calculations
• Creating visualisations (graphs, line charts, tables and so on)
  
  2. Power BI Online (also called Power BI Service) – PBIX reports are published to Power BI Online, where the visuals in a report can be viewed by anyone with access. Reports can be organised and saved into Apps, which are like online folders while dashboards, taking the best bits of multiple reports can also be created.

To start building reports then, we need to need to download Power BI desktop. If you haven’t done that already, then you need to go here.

Alternatively, if you’re using a company computer, you’ll probably need to get your company IT team to give you permission to download the software. Placate any IT team furrowed brows by telling them that the software is:

• Microsoft and therefore pretty trustworthy
• going to make you more productive and efficient
• free to use and download.

Opening Power BI for the first time

Once you’ve saved Power BI to your computer, open it up and you’ll be presented with a pop up menu which asks if you want to connect to data. You can close through clicking the cross in the top right hand corner.

After that you’re into the report screen where you create visualisations … but we haven’t got any data yet! For that we need to get into the PowerQuery screen which we can do through clicking on the button ‘Get Data’ on the ribbon (just like in Excel!). Please note that Power BI desktop is updated monthly and therefore the screen you see may look slightly different to the version in the video which was recorded in 2019. Back then you pressed a button called ‘New Source’

Once you select Get Data, a menu will pop up showing you the options of different connectors to different data sources to which you can connect, again just like in Excel. Select your connector option such as Excel for an Excel workbook. Alternatively, if you’d just like to have a look around PowerQuery in Power BI, then you can search in the search bar for ‘Blank Query’.

Stage 1 – The PowerQuery screen in Power BI

There’s not a lot extra to add to the video to be honest here. As you can see in the video, or if you have taken yourself into Power BI, the PowerQuery screen in Power BI is almost identical to that in Excel. There are a couple of new tabs that have been added to more recent versions of Power BI, but these contain much more advanced options than we need to go into in this course. So we won’t concern ourselves with those at the moment.

The key button to consider for the moment is how to get out of this screen, and get back to the visualisations page. In Excel, in the Home tab, we selected ‘Close and Load’ but in Power BI, in the Home tab, we select ‘Close and Apply’ (it’s in the same place as ‘Close and Load’ in Excel).

Stage 2 – The Data Model Screen.

Once you press ‘Close and Apply’, Power BI will close the PowerQuery screen and then bring you back into the Report View screen where you can create visualisations. Unlike in Excel where you can’t use any part of Excel if the Excel PowerQuery screen is open, you can actually have the PowerQuery screen and the Report View screen open at the same time in Power BI.

To get into the Data Model screen, look on the far left hand side of the screen for three buttons stacked on top of each other. Look for the button in the middle with a table icon upon it and hover over it until it says Data. Select it to get into the Data view.

The purpose of the Data Model view is very different from what you can do in the PowerQuery Editor. There are tasks that you can perform in both, such as creating new columns but by and large, PowerQuery is all about taking unclean, unformatted data and cleaning it, pruning it, adding data in, removing data we don’t need and so on. PowerQuery shapes data so that it can be best used for analysis. The Data Model view is all about creating calculations, to do that analysis.

So now we’re in the Data Model Screen, we have:

1. As usual with Microsoft products, a number of tabs along the top along with a ribbon underneath enabling different commands.
2. smack bang in the middle of the screen, a large area for viewing data
3. on the right hand side of the screen a list of tables and fields within those tables of the data that has been transformed in PowerQuery.

Now while this course is a PowerQuery course, not a Data Model course, here are a few pointers for how to use the Data Model.

Filtering data

It is much faster to filter data in the Data Model view to investigate and analyse it. You may have noticed that filtering data is quite slow in the PowerQuery Editor. In the Data Model view it is (depending on the amount of data) almost instantaneous.

Additionally, filtering in PowerQuery removes rows from the dataset so that it doesn’t get through to the Data Model. However, filtering data in the Data Model does not remove rows from subsequent calculations or the visuals in the report view.

Creating calculations

As in PowerQuery, columns can be calculated in the data model. However, there are a second form of calculations called ‘measures’ which can also be calculated. You won’t find these calculations in any table, but they do show up in the Field List on the right hand side of the screen. These measures are like the implicit aggregation calculations that are performed when you drag a field into the field list of a PivotTable. For example, if you drag a numeric value field into Value pane of the Field list of a PivotTable, a PivotTable will automatically perform a SUM calculation over the numeric values. If you drag in a non-numeric value field, then the PivotTable will perform a COUNT calculation. Power BI does the same thing in the Report View, but you can also write simple (or extremely complex!) calculations called ‘measures’. These measures use a separate code to M called….

DAX

I know what you’re thinking. I need to learn another code to make Power BI work?!

Well no to be honest. You can comfortably use Power BI, creating reports without using DAX as Power BI’s Data Model view is at it’s base a pretty pivot table. You DO need to know PowerQuery though!

You can just drag fields into visuals in the Report View and Power BI will aggregate the data, as it would in a PivotTable. This is perfectly fine and the ideal place to start as a beginner.

However, you won’t get the most out of Power BI if you do that so here are a few options:

• Use the Quick Measures option. This is a ‘drag and drop’ option allowing calculation of some quite complex measures – let Power BI do the work!
• Start to learn DAX. Dax at it’s base is very similar to Excel formulas. For example it is based on aggregation formulas that you probably use every day, such as SUM, COUNT, AVERAGE, MAX, MIN and so on. Unlike M, which is still quite specialised and not widely known, DAX is a much more widely understood language and consequently, there’s plenty of literature, online courses, and YouTube videos on the subject.

Stage 3 – The Report View

Back to the Report View then which is where we put our Visualisations together. To get here, go back to the left hand side of the Data View Screen and find the Data View button. Found it? Click on the button above which has a graph icon on it.

The Layout

Once again we’re greeted with some tabs at the top, along with the ribbon enabling certain commands. These are pretty similar to those you’ll find in the Report View (and the Relationships View).

Taking up most of the screen we have the ‘Canvas’, where we can drag and drop visualisations and on the right had side we have three panes: the Field List, the Visualisations Pane and the Filters Pane.

The Fields List

On the right hand side of the screen we have a Fields pane, like we have in the Data View or when compiling a PivotTable. As usual, this holds all the tables and fields of tables that have been loaded into the Data Model from Power BI. You can populate tables or graphs in the Camvas with these fields.

The Visualisations Pane

Next to this we have the Visualisations pane. At the top of this pane, we can see the different types of graphs and tables that can be used including bar charts, line graphs, tables and slicers. These can be selected and they’ll populate a placeholder on the Canvas.

Below the Visualisation selector, there are three buttons (or two if you haven’t selected a Visualisation). The button on the left which looks like rectangles made out of dotted lines, allows us to select the fields we’d like to use in the visualisations. Selecting this button brings up a list of windows below into which you can drag and drop fields from the Fields List, just as if you were selecting information from a Pivot Table. The number of windows and names of the windows differ depending on the visualisation you’ve selected.

The second button beneath the Visualisation selector looks like a paint brush and allows us to format the Visualisation we’ve selected (or the background of the page if we haven’t selected a Visualisation)

The final button looks like a magnifying glass and this is our analytics tab which gives us additional options for analysis in certain visualisations.

The Filters Pane

The Filters Pane allows you to put filters on visualisations, pages or entire reports, just like the filters pane in the Fields List of a Pivot Table. Unlike a PivotTable, where you can only put a filter on a single PivotTable at a time, in Power BI, you can put filters on any visual you like. Simply drag and drop the fields that you wish to filter your report by from the Fields list and pop them in the Filters pane and start filtering.

So there you have it. A very quick introduction to Power BI and how it is used. Power BI is a wonderful tool and while it can be used as simply as we have outlined in this lesson, it can also be a good deal more complex and we really recommend taking a course in it to see the many possibilities that it offers.