Cricket and the Power BI Designer

With the 2015 Cricket World Cup concluded, we’ve all had a few days to rest and think about other things… okay, enough of that. Back to cricket!

Cricket fans love statistics, and the Power BI Designer is a perfect tool to bring all sorts of different cricket data together, mash it up, and make really cool visuals from the results. If you haven’t given the Power BI Designer a try, you can download it here.

The Designer has a couple different views – the Query view (where you can connect, transform, and combine data), and the Report view (where you can create visuals from all that cool data). Here’s a look at the Query view in our cricket file. Notice that Query is selected, in the lower left corner:

And since we love creating visuals in Power BI, here’s a look at the first Report page in our cricket file. Notice that Report is selected now (again, in the lower left corner of the Designer).

You can save your query work and reports right in the Designer – the Power BI Designer File type (which as the .pbix association) saves all your work, and can be shared in the Power BI Service. What to follow along, or see all the cool work in this file for yourself? You can download the Power BI Designer file, and play with it to your heart’s content.

How did we do this? Let’s take a look at the steps we took to create this Designer file. And as you read along, feel free to imagine the sort of things you could do with the Power BI Designer, with your own data or your own data analysis efforts.

Step One – Connect

First, we need to connect to interesting data. We used www.cricketarchive.com, which has all sorts of publicly available data. On that site, data is organized into tables such as players, teams, seasons, and so on.

The Power BI Designer can connect to all sorts of different data sources. Whether the data is in a workbook, a database, an Azure service, or a website table, the Designer can connect to any or all of them. The following image shows just how many data sources the Designer can connect to, and more are planned.

We connected to the following Web page:

http://cricketarchive.com/Archive/Records/World_Cup/Overall/Batting/Most_Innings_Boundaries.html

We made a bunch of other connections to that site, each of which is an individual query, which you can see from the Queries pane in Query view.

Step Two – Transform

In the Power BI Designer, you can shape any data source in all sorts of ways – without changing the underlying data source. That point is worth clarifying: when you transform data (remove columns, change data types, merge data sources, so on) in the Power BI Designer, you only transform your view (or instance) of the data. The underlying data source remains unchanged.

Getting back to our cricket data, we performed all sorts of transformations, in multiple queries:

Renamed columns
Split columns
Changed data types
Removed null values from columns
Merged with additional data sets
Other transformations, too

We specified these transformations in Query, which is where queries are created, modified, managed, or changed. Any transformations made in a query are recorded in the Applied Steps section, in the order they were applied. That way, when Query runs the query again (remember, against the original and unchanged data sources) your view of that data (transformed by your applied steps) can be created again with fidelity.

Now that we used the Query view of Power BI Designer to transform and mash-up the data how we want it, we need to visualize that data in Report view. And that brings us to our next step.

Step Three – Visualize

With the data transformed how we want it, it’s time to find interesting facts, trends, and insights. We select Report from the bottom left corner to change to Report view. There are a bunch of report pages in the Designer file which were created for this post, which you can downloadand play around with it yourself, if you want.

To create a new report page, just right-click in the left pane of the Report view (where the report page thumbnails are shown) and select new page.

A new page is placed between the two nearest Report pages. If you right-click an existing page, you also have the choice of deleting the selected Report page.

Once a new Report sheet is created, you can simply drag a field from the Fields pane, drop it onto the canvas, and the Designer creates a basic visual for you (based on the field you dropped). Then drag another field onto the canvas, or onto the visual you just created, and the fun begins.

You can play around with the fields until you get what you want. In the following image, three fields have been dropped onto the canvas – first Centuries, then Player dropped on top of it – and now we’re dragging Half centuries onto the existing visual too.

Once you release that field onto the existing visual, it’s automatically updated.

With a bit of creativity, you can build all sorts of interesting visuals, like those found on the following Report pages.

The visuals are interactive, too. Here’s the same report, with 2011 selected:

There are many Report pages in the Designer file (the .pbix file), each with many visuals. You’re welcome to check them out for yourself and explore.

Step Four – Connect to Power BI, and Share

Reports are great, but they’re even better when you can share your efforts with others. The Power BI Designer is engineered to be used with Power BI. To connect your Power BI Designer File (.pbix file), log into Power BI and select Get Data.

In the screen that appears, select Power BI Designer File from the selections in the left pane, then select the big yellow Connect button to the right.

Once Power BI connects to your Designer file, it imports the file and makes it available in your Dashboards, Reports, and Datasets sections. From there, you can use the reports you created to build dashboards, and share them with others.

Now you can view your Designer file on Power BI as a Dataset…

… or as a collection of Reports…

…or you can create Dashboards from the Designer file data.

Sharing is easy too – once you’ve created a dashboard, just select Share Dashboard.

What’s next?

It’s always fun to apply critical data analysis to something like Cricket. And yes, you can even apply all this data analysis and business intelligence to… business cases. But with cricket, you get the benefit of showing how well your favorite team does in certain areas, which also brings bragging rights, clarifications on which team is really better, all of those good things… once the data is visualized appropriately.

There are all sorts of things you can do with the Power BI Designer and Power BI. We hope you’ve enjoyed this post – check back soon for more fun, cool things you can do with the Power BI Designer and Power BI.

הבלוג של Microsoft Power BI

Step One – Connect

Step Two – Transform

Step Three – Visualize

Step Four – Connect to Power BI, and Share

What’s next?

Links

About the Bloggers