T-SQL Tuesday #75–Power BI

This month’s host is Jorge Seggara, the @sqlchicken, who works for Microsoft. A busy schedule caused a slight delay, so we’re posting the third Tuesday of this month, but that is OK. This is a great topic for T-SQL Tuesday.

Power BI Data

While Power BI is a great visualization tool, you can’t do anything without data. That means you need to find data, which is both easy and hard. Easy if you’re working within your own organization on a specific project. Slightly more complex if you want to look at data out in the world.

However I saw this in a talk last year and I was amazed. This is the type of thing I’ve written before, and it’s cumbersome and problematic. I would think that SSIS would have made things this simple years ago.

I love sports, and wanted to play with some sports statistics awhile back. Finding good data is tough, at least in a format like CSV, that you can easily import. However Power BI makes this easy. Start up the desktop and you’ll see this:

2016-02-10 14_07_40-Calendar

Right away Power BI wants to get data. Click on this and the Get Data dialog opens, with lots of choices.

2016-02-10 14_07_57-

However if you pick “Other”, you’ll see one more that I love. Web.

2016-02-10 14_13_55-Calendar

Click this. You get asked for a URL. Any URL.

2016-02-10 14_14_35-Calendar

I happen to have one handy. After the win for Denver in Super Bowl 50, I thought I’d look back at Mr. Manning’s career.

2016-02-10 14_14_44-Calendar

I take that URL and drop it in the dialog.

2016-02-10 14_14_52-Calendar

Once I click OK, this will analyze the URL for tables of data. In this case, I get quite a few.

2016-02-10 14_16_32-Calendar

Now, I can click each one to see what data this is. This isn’t what I want

2016-02-10 14_16_32-Calendar

But this is.

2016-02-10 14_16_37-

I now click “Edit” at the bottom to clean my data. I could just load it, but there are a few issues.

2016-02-10 14_16_47-Untitled - Power BI Desktop

I see all the data in the designer, and I have lots of options for working with this data.

2016-02-10 14_17_11-Calendar

First, since I’m going to do a comparison, let me rename the table.

2016-02-10 14_17_02-Untitled - Power BI Desktop

Next, I see the steps below the name. I’ll add more steps, but I’ll do this in the designer GUI. First, let me remove the last row, which is a career summary.

2016-02-10 14_19_28-Calendar

In this case, I’m only removing one row.

2016-02-10 14_19_36-Calendar

Now, I want to remove a couple columns. In my case, I don’t care about a few of the data items, so I’ll pull them away. I can right click a column or choose “Remove Colums” in the ribbon. Either way, I get rid of QBR and Team.

2016-02-10 14_20_50-Untitled - Query Editor

Now I’ve got a nice year by year summary of Peyton Manning’s career. When I close and apply the query, my data is loaded into a data set for use by my Dashboard. I can then repeat this, and I’ll have two sets of data.

And, here’s my PowerBI Dashboard. It’s not terribly useful, or interactive, but it’s got data from the web that I didn’t have to copy or move.


About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

6 Responses to T-SQL Tuesday #75–Power BI

  1. ODed Dror says:

    Is there anyway you can email me the file? I’ve try to replicate the report but I got stuck
    With the two tables and theirs name (how do you filter on names if you have two tables?)
    Or at least show the data model
    Oded Dror

  2. Pingback: T-SQL Tuesday #75: Round Up | | SQLChicken.com

  3. Pingback: T-SQL Tuesday #75: Round Up - SQL Server - SQL Server - Toad World

Comments are closed.