How to Get Started with Power BI – A Guide For Beginners
Nick Hilton | 29 September 22 | 11 min read
Welcome to "How to Get Started with Power BI – A Guide For Beginners"
Who is this article for?
Anyone looking to create their first report
Anyone looking to introduce Power BI to their organisation
Anyone who just wants to know more
If you’ve already got to grips with how to get started in Power BI and are looking for your ‘step 2’ then scroll down to the resources section where there are some helpful links to help take you from step 1 to Power BI guru.
What is Power BI?
Power BI is a business intelligence tool developed by Microsoft used for accessing, analysing, and visualising data, it has quickly become the most popular BI tool on the market and it’s widely considered to be the best, and I’d say it is (dependant on your specific requirements of course.)
You can connect to and bring in pretty much as many data sources as you like, manipulate the data if you need to and then load it so you can create reports using that data. When you’re done you can publish those reports or share the file, generally this is to an online portal called ‘The Service’ where it is shared with other users in your organisation. Alternatively, it could be published to the web for anyone to view or the Power BI (.pbix) file itself could be shared for some one else to use in Power BI desktop.
Power BI isn’t a standalone tool either, it exists as one tool in a whole range of services which can be extremely powerful. Microsoft have made this huge ecosystem called The Power Platform which is a collection of tools built around data that caters to an enormous number of needs including things from automation, reporting, training AI models, and building apps, websites, and virtual bots. So, your Power BI journey can start as loading one Excel file in and making some jazzy visualisations all the way to using it as a way for users to access data whilst clicking buttons to drive automated processes and feeding data back in via embedded apps. There’s a lot of ground between those two points but it’s exciting how much can be achieved with it. Best of all it’s built to be intuitive and accessible so if this is your first foray into the data or BI world then this is what you’re looking for.
Here are a few examples of different things I’ve worked on in Power BI over the years and just to show how varied it can be I’ll include things from the first time I used it all the way to more recently:
Loading in 10 massive csv files to access large amounts of data in one easy place
Making a report for monthly performance reviews from Excel files
Similar reports from SQL databases and other cloud connectors
A tool which would show any transactions that couldn’t be reconciled in other systems
Tools for other colleagues to quickly extract key customer data
A client report generator where salespeople could instantly produce 6 month reviews
A live diagnostics tool for teams to monitor multiple aspects of a system
Reporting embedded in a secure portal (Power BI embedded)
A report which analysed every pub in the UK by post code data (this was fun!)
And so much more!
What You’ll Need
There are a few different versions of power BI that you might hear about:
Power BI Desktop – This is what you’ll be downloading to create reports in
Power BI Service – This is where you’ll be publishing and sharing reports to
Power BI Report Builder – An alternative tool for building paginated reports
Power BI Embedded – A way to use Power BI to embed data and visualisation anywhere that allows embedding. E.g. client portals, CRM systems
Power BI Mobile – An app for viewing reports that have been published to the service
All you need to get started is Power BI desktop and a computer which isn’t on its last legs (as your data gets bigger and more complex your PC may struggle depending on its specs). You don’t need an account with an organisation, a licence, or to pay. Head over to the Microsoft store if you want it to automatically update itself each month or go here for the executable. Once you’ve got it downloaded follow the install wizard and you’ll be ready to go.
First Steps – Getting and Loading the Data
In this scenario we’re going to load in a single table from Excel, we won’t get into modelling or any major transformations for now, we’ll just focus on the basics.
1. Open Power BI and close the screen that pops up with the introduction on it
2. You are now looking at the report canvas. This is where you’ll be building visuals and displaying your data
3. First, we need to import our Excel data. You can click on any of the obvious options, but as you’ll likely be using a range of data sources in the future let’s go the proper way and navigate to get data in the ribbon at the top. Then get data -> Excel Workbook
4. Once you’ve selected your file Power BI will open a further navigation window to make sure it’s looking at the right data. In this case it’s found the table in the Excel file (as a table is a self-contained entity) and it’s also intelligently recognised the sheet that table sits in and would cut out all the blank cells around the data. Both options would work perfectly in this scenario. Select the option you want and then click ‘transform data’ which will take you to the next area where we can check our data. Unless you’re loading some data for a quick test or something like that then I recommend never going straight to load. You will benefit massively from spending the time looking at and formatting your data.
5. Okay introductions first, this is Power Query. Power Query is the layer in Power BI where you’ll be making sure your data is loading right and then formatting it. I love this part of Power BI and it’s immensely satisfying to transform the data with a few clicks. We’re only going to fix some formatting, but Power Query is chock full of potential with a huge variety of tools to use and it even has its own language (‘M’)
6. The first thing we’re going to do is go through make sure each column is the correct data type. The software does try and figure that out automatically but it’s always worth checking. (Note: later versions of Power BI may be improved and have better detection than at the time of writing) Here the order date column was correctly formatted as a date but the shipping date wasn’t. To change that click on the little icon on the left of the column header you want to change and select date from the dropdown.
7. For me there were a few currency columns to the far right which had come through as text. Follow the same process of clicking the icon on the left of each header except this time choose ‘Fixed Decimal Number’
8. The good news is if anything goes wrong it’s very easy to undo. Every action in Power Query is listed as a query step on the right of the window. Removing a step is as easy as hovering over it and clicking ‘X’
9. If you’re at all intrigued by Power Query then use this opportunity to click some buttons and see what you can do. A lot of things are very intuitive and self-explanatory, especially if you’re used to using Excel to a fairly intermediate level.
10. Once you feel the data has nothing more that needs to be done then click that wonderful ‘close & apply’ button in the top left.
Part Two – Power BI (Reporting Layer)
Finally, here’s the bit where you get to make visualisations! As mentioned earlier if you had more complex data it’s now that you would be likely to get busy doing data modelling (joining tables together) but as we’ve got some straightforward data we get to skip that and go straight to getting some results.
This big white page is ‘the canvas’. More specifically it’s your canvas and while I’m going to run through a scenario of putting a visual or two together the best thing to do is just test and try everything and find all the interesting different ways to display your data. In previous blog posts I’ve alluded to how I spend some evenings having too much fun with data, well I kid you not the words: ‘Damn this scatter chart is looking NICE!’ was probably heard coming from my house around 9pm one night. 😂
Here we’re going to add a few elements to the canvas. A chart to show number of orders over time, a donut chart of order split by region, and a counter of how many orders there are overall.
1. Let’s start with orders by date. On the right of the window your list of fields is displayed (if you had a second table it would be listed under table1). Click the dropdown arrow and then click and drag order date on to the canvas. You could also tick the box to make it appear in the default location.
2. Next you can change the visual to a line chart by changing the visual in the top right-hand corner
3. Now we need to drag ‘Order Id’ from the fields pane into the ‘Y-Axis’ box on pane next to the left of it, this is where you control the fields that are available in your visual. You now have a line chart showing number of orders by date! But it does need sorting out slightly…
4. The date automatically presents as a hierarchy so rather than having a data point for every single date contained it shows them for each year, quarter, month, or year (this can be change to the former). While the end users can drill up or down themselves you’ll want to make sure the default view is one that offers value. Hover over the visual and in the top right corner click the downwards facing fork until the title says month in it. If you click too far you can navigate back up the hierarchy by clicking the nearby up arrow.
5. You can move the chart around and resize it. We won’t go into formatting and design here but the resources I leave at the bottom certainly will expand on that.
6. Donut chart: To save time and precious effort copy and paste your last visual with CTRL+C and CTRL+ V, get rid of the order date field by clicking x on it in the visualisations pane, and then in the same pane change the visual to a donut chart.
7. At the moment your donut is probably looking a little blue but we can very quickly get some detail and colour in there by dragging the region field into the detail box on the visualisation page.
8. At this point your page should be looking something a little like this
9. We have one final reporting requirement, a data label showing the overall number of orders placed. One last time copy and paste a visual and then remove the field from the visualisation pane that isn’t ‘Order ID’.
10. Change the visual to be a ‘card’ which is the small square with 123 on it in the Visualisation pane and then position it how you want!
Now the data is on the page click on your visuals to see how they interact. If you click on the London region for example in the donut chart then all the visuals on the page will react and filter to your selection. That dynamic filtering is a large part of what makes Power BI so powerful compared to traditional reporting tools.
Last bits, next steps, and resources
I hope this post has been helpful in providing a basic introduction to Power BI. A lot of resources go in heavy and have a lot of assumed prior knowledge so I’ve tried to keep this concise to build a foundation which you or your teams can expand on. The next thing I would do is keep messing with this data in the file we’ve created, try and create the best executive report you can, or a logistics style dashboard! Alternatively find some data of your own (wherever that may be, not just Excel) and see what you can create.
BI tools like Power BI are great to learn or make use of because of how much value they bring. That could be in the form of value to a business, or value to an individual by enhancing their skills or other projects. Some people will love Power BI because it allows them to create and design stunning reports, others will love it because of how quickly they can pull data out of it, others will love it because of how simple it is for them to do some major merges and transformations in Power Query. Well ‘love’ Power BI may be a bit strong, for you anyway, not for me! 😍
Here are some of my favourite resources for taking those next steps:
Kevin Stratvert – This guy is a Microsoft power house. Great introduction to Power BI and lots of other content on other software you may find interesting
Guy in a Cube Introduction to Power BI – This channel is the number 1 for Power BI, check out this video on getting started then the one below on next steps. As you progress make it your mission to work through their content, it will take your skills to the next level I guarantee it.
How to Power BI datatraining.io This channel is a gold mine for design focused content. Once you’ve got the basics down head over here to get inspired.
Which takes me to a final tip, check out this previous post to get a checklist on some fundamentals for design you will want to think about. Most reports that people produce are glorified data dumps, and don’t get me wrong having that data in Power BI alone is a huge benefit but well thought through design, both functional and aesthetic is the difference between producing something ‘just good enough’ versus really impressing your audience.