Join Gini von Courter for an in-depth discussion in this video Reducing data, part of Power BI Pro Essential Training.
- [Voiceover] This is a lovely dataset, but it's not yet in the shape that I want it to be in to work with it in Power BI, to use it for analysis. For example, we have a total down at the bottom and we don't need that. We also have the District of Columbia and if your intention was only to compare states you would be eliminating the District of Columbia from your dataset. If you wanted to do that it would be easy, because what the District of Columbia and the United States row have in common is they were never admitted, which a bone of some contention to the folks in the District of Columbia.
What I'd like to do is edit this and there's a Query Editor tool to do that. The way we get there is we go to Edit Queries. And notice Untitled, Query Editor and here we are and just tools in abundance. Again, covering all of these is well beyond the scope of this course, but there is another course where you'll see how to use all of these different tools in the Query Editor. There are some nice visual hints for us here.
For example, this is a text column, it has A-B-C, a number column, one, two, three, so information that's provided even without us having to click and look at anything. You notice that we have lots of null values in the places where we presumably didn't make a, take a census. When we're looking at the data rather than looking at the Query Editor those simply show as empty. Let's begin by applying a filter, and let's drop out United States.
Okay, now I should have 51 rows, because I kept the District of Columbia and if you look in the lower left hand corner on the status bar you'll see 11 columns, 51 rows. On the right hand side, in the Query Settings what you see is a step that I applied, which was to Filter Rows. If we choose not to Filter Rows I can reverse this by simply removing that step. And if I do, notice, back to 51 rows. But I really do wanna get rid of that total, it has no place here, because it's the summary of everything that's already there.
I'd like to make one other reduction here as well. Because this dataset is for a fixed period of time ending just before the American Civil War there are States where we never took a census. We never went up to Alaska during that time. And that's one of the ways we know the difference between null and an actual number. There were people there, but we weren't counting them. The easiest way for me to get rid of the rows that have no data is to go to 1860 and in 1860 if it's null let's not show it.
And now I have 43 rows of data, 42 states and the District of Columbia. All of which, by 1860 had a number. Although you notice, for example, that Colorado just got in. Their first census was in 1860. This looks good though. I like how it looks and I'm ready to continue to work with this. We have successfully reduced our data. There are other tools that would allow us to do that.
We can Choose Columns, we can Keep Rows at the top or the bottom or a range of rows, we can Remove Rows at the top or at bottom or a range of rows if we wish. Sometimes what you want to do is take a large dataset and just take a subsection of it to work with, give me the first 500 rows out of 50 thousand, so that I can use those rows as a model to determine what kinds of things we might like to use for visualizations. That's what these reduced rows features are for.
I'm going to save this now, I'm simply going to click Save, it says there are pending changes in our queries, in my queries, that haven't been applied, I want to Apply them, whatever those changes were. And now I'm going to save this. I will save it in the Chapter eight folder. Note that the file type is one of two, PBIX or a Template File, which is relatively new, a nice new feature in Power BI Desktop, and I want to save this as ReducedStates.
Sounds like a science fiction movie, but that will work for me. See you in the next movie.
- Signing up for Power BI Pro
- Connecting to data sources
- Uploading data such as CSV and XLS files
- Creating reports, visualizations, charts, and maps
- Filtering, sorting, copying, and pasting visualizations
- Downloading custom visuals from the gallery
- Modifying existing reports
- Creating and managing data dashboards
- Querying data with Power BI Q&A and Microsoft Cortana
- Sharing report and dashboards
- Using Power BI Desktop and mobile apps