There are a ton of cool features in Power BI that you may not be familiar with, even if you have been using the tool for a long time. In this blog post I’m going to walk you through some of the cooler features that I’ve found useful while working with clients. Just maybe, some of them will (tab)blow your mind! Here is a list of what we’re going to cover today:
- Web Scraping
- Power Query Basics
- Single Cell Editing in Power Query
- Ribbon Chart
- Detect Data Types
Get ready to have web scraping be easily than you ever believed it could be! Normally, if you wanted to get data stored in html format in a website you would have to use a R or Python package to get the data, either export it as csv or integrate it with your BI tool of choice, then visualize the data. Power BI lets users access this data straight from the web at a moment’s notice.
First, click on Transform Data so that we can bring our data in via Power Query Editor.
Once in Power Query Editor, select New Source from the left hand side of the Home ribbon and then select the Web option.
Selecting Web will bring up a box that allows you to select from either Basic or Advanced. In this instance, we will just be using the Basic functionality. Enter in the website URL that you are trying to scrape from and then click OK.
Power BI will then bring you to a table like below with all of the different sections of the web page available for import. Select the table you want to bring in, then click OK.
Now you will have your section of the web page that you want in Power Query Editor ready to be played with. I know, this seems way too simple. But this is all there is to it!
Power Query Basics
Power Query is the preeminent feature of Power BI. The ability to transform, edit, and model your data within your development platform sets Power BI apart, especially when it comes to self-service analytics. Data rarely comes modeled the way end users need it to be, so equipping them with the power of Power Query can really bring your self service analytics to life.
In a lot of ways, Power Query is like being able to use Excel to edit your data set. Many find this intriguing because Excel is so flexible in allowing you to edit every cell of every data set in a way that is very hard using other platforms.
Here is a quick overview of some of the Power Query capabilities that you may or may not know already.
Power BI allows users to group their data by different columns within their data set. Grouping by a field allows for preset aggregations so that your end visualization does not have to do certain calculations on the fly. Aggregating by a Region or Country (as an example) will increase performance as your work has already been done on the query side and won’t have to be done via a DAX calculation.
If your data set is not in the right structure, you can use the transpose feature to correct it. Say your data set is extremely wide, meaning there are tons of columns that you actually want to show as rows, or if you have lots of rows you want to show as column, clicking the Transpose button will do just that. It will transpose your data so that your columns are now rows and your rows are now columns.
Although this feature is not a major feature, it is very useful. When clicking on a column in Power BI, you can click on the Extract drop down which gives you several options to select from including length, first characters, last characters, range, text before delimiter, text after delimiter, and text between delimiters. These different selections will allow you to do a number of different text parsing and manipulations. This is often useful when trying to break out naming conventions that are often structured by characters in a string.
Single Cell Editing
The best thing about Excel might be its flexibility. If a value in cell C4 is incorrect, you can click into the cell and correct it’s value. Fortunately, Power Query allows you to do that exact same thing.
In this example, we have a value in one of our columns that has an asterisk that we don’t want to be there. We want to be able to display just the player’s name and not have an asterisk.
Go to Power Query Editor and select your data set on the left hand side. Next, go to the cell that you want to edit and right click. The menu in the image shown above will appear. Click on the Replace Values option. This will bring up the panel below. It is very much like Excel’s find and replace feature. Type in the value that you want to show instead of the current one and click OK. Power BI will replace the original value with the new one.
Being able to edit individual cells is such a nice feature that provides a lot of flexibility to users that may need to just edit a hand full of cells.
Detect Data Types
When creating a data set with the Advanced Editor feature of Power BI, I ended up with a final data set that showed every column in the ABC123 format by default. as you can see below, not all of the data is in the same format. Some are integers and some are strings, so I want to change each of those columns to be the correct data type. What I don’t want to do is go through every single column and change the data type from ABC123 to Integer or String. Here’s how you do it in one fell swoop.
Click on your first column then press Ctrl + A. This will select your whole data set. Now go to the Detect Data Type button in your Transform ribbon and click on it. Power BI will think for a second and then your column formats will be automatically changed. Although this is not guaranteed to be right 100% of the time, it has a very high success rate. This feature looks at the first 200 or so rows of your data set and then determines the most common data type within that sample and applies it to the whole column.
There are a lot of cool charts in the Visualization pane of Power BI, but one that stands out to be is the Ribbon Chart. Although the business use cases for this chart may be more limited, the ability to choose this chart type as a preset chart is awesome.
This chart is great for showing how values flow over time. In the report below I showed how the great Chicago Bulls teams of the 1990’s changed over time in terms of who was scoring most. In this case, Power BI makes a fairly complicated chart type very easy to make.
Do you have more questions about Power BI? Talk to our expert consultants today and have all your questions answered!