Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Query View in the Power BI Designer

0 (0 Likes / 0 Dislikes)
Let's take a look at how you can connect to data and shape it as you import it into the Power BI Designer. From the getting started screen here, I'm going to click get data. This will give me a list of all of the data sources we could connect to. In this case, I'm going to use a web page. I'm just going to search for web and select that option. Now, here when I hit connect, I'm going to get prompted for a URL. In this case, I'm going to use the ATP World Tour site. There's a table here on this page which shows us all of the tennis players, the points they've earned in this latest tournament, and I'm just going to copy the URL and paste that back into the designer. The designer will now go to that page and look through the HTML for any tables or data that we could use. The table there is the one I want. And I get a little preview of the data. I could load it directly, but I'm going to shape and transform this data by clicking edit query. In this query here, I get a preview of all of the data in the middle, I get a ribbon with a set of transformations, and changes that I can make to that data. And on the right hand side, I get some query settings, and I get a list of applied steps that I'll build up as I make more transformations to this data. I'm going to focus on this rank, name, and nationality column. I want to split that into three. I'm going to start by choosing to split that column. The first thing that I'm going to split it on is a dilimeter, I'm going to use the space. So this will split on the left most space, and give us a rank column, and then a separate name and nationality column. Here's the rank column, here's name and nationality. So I'm going to split this column again, and this time I'm going to split it using the left bracket. So I'll choose a custom delimiter, and the left bracket. Again, I only want to split this once, then I'll hit OK. Finally, this last column, the nationality column, I don't need to split this anymore, I just need to replace that right bracket. So I'm going to choose replace values, type in the right bracket, and replace it with nothing, so I'm effectively deleting that bracket. So, I've made those three transformations, let's just rename these columns as well, double click on the header, type in the name, hit return. Let's do the same here. This is the country that the player comes from. So I can see over on the right hand side all of the different steps that I've built up— connecting to the source, navigating to the table, various splits and replacements that I've made, and then those renames. I can replay through these if I want to see what those steps were. I can always also use the gear icon on the right hand side here to go back and make changes to the settings for each of the individual steps. But I'm done now, I've got a good set of data, and I can start building some visualizations. So I'm going to come down to the bottom left and choose a report view. The first time that you swap to this report view, we'll go and load that data, so cache it from that website into the designer. Now we've got the data loaded, I can see all of the different columns on the right hand side in the field list here. To create a visual, I just grab the field, and drag it over onto the canvas. So we see rank by name by default, I probably don't want to see rank, I probably want points, and I can choose something like the country. Because this is a geographic field, we'll get a map by default. Again, I probably want to look at points rather than rank, so let's drag that down and replace that rank value with points. So I've got a really simple report here, and this is fully interactive.X So now if I click on one of these bubbles, it will highlight the players from that country. So you've seen in just a few clicks how I can grab data from a data source, transform it, and shape the data and load it into the Power BI designer to build some reports. Thanks very much!

Video Details

Duration: 4 minutes and 7 seconds
Country:
Language: English
License: Dotsub - Standard License
Genre: None
Views: 1
Posted by: csintl on Oct 28, 2015

The query view of the Power BI Designer Preview gives you powerful control of your data. Learn how to shape and clean your data in the Power BI Designer.

Sign up for the Power BI Preview at https://powerbi.com/dashboards and then log in at https://app.powerbi.com

Caption and Translate

    Sign In/Register for Dotsub above to caption this video.