Vote for the improvement to Filtering:
excel.uservoice.com
While thinking about the Filter problem, I decided to check to see if the data profile improvements had been added to Power Query in Excel, and they are! You can now see how many distinct and unique items there are in each column.
UserVoice Pages
While thinking about the Filter problem, I decided to check to see if the data profile improvements had been added to Power Query in Excel, and they are! You can now see how many distinct and unique items there are in each column.
Transcript of the video:
Learn Excel for MrExcel Podcast Episode 2288.
Data Profiling in Power Query.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Why a great question came in today.
Someone was trying to use the Filters and said wouldn't it be cool if here next to each filter item in the drop-down menu a Count would appear so that we could see how many there were in each item and I said that is a good idea.
I bet someone UserVoice already has this and sure enough there's one out there but only 10 votes.
Let's see if we can get that up. That would be a great thing.
So down in the YouTube description I'll put a link to this this URL.
If you could go give us a vote that would be great.
But I said, You know, I know that there's a feature coming in Power Query. I just don't know if it's there yet, right?
So I wanted to insert table, make my data here into a table, and then on the data tab say from table or range.
You know, and at first like nothing looked Like there was anything new, right?
But I said Alright, where would they have put this? Not Transform, not Add Column.
Let's see what's out there on View and sure enough, its on View.
We now have this awesome new set of things.
First off, we can see things in Monospace in case you want to see if things are really lined up or not. So we have that one.
Column quality shows whether or not there's any error. So here everything is 100% valid.
No errors, no empty.
If there were errors then you can come in here and Keep Errors or Keep Duplicates or Remove Empty or Remove errors. So nice little bit of UI there.
And then column distribution. So I choose a column.
Actually I don't have to choose a column, they're going to show for all.
So here in Region I have three distinct and 0 unique.
In date, 440 distinct 340 unique. 7 distinct sectors. No unique customers.
27 distinct customers, 10 distinct quantities, which kind of shows that this data might be fake data, right?
And then the other one here?
Let's take customer and we asked for the column profile.
And it will show us that there are 563 values there, 27 distinct values. The smallest item is ABC stores.
The largest is Walmart and when I hover there 66 rows for Exxon, 65 rows for Walmart or choose another column an I get to see those items right? I saw this in beta out of the Microsoft Summit.
I don't know a year ago two years ago and I kept hearing all this is coming right and I said, well, that'll be great when it gets there. But it's here, at least in Office Insiders.
It's here. So check it out.
I didn't get an email saying, hey, this is the greatest thing ever. Go check it out.
I just was hiding back there on the view tab.
Although the cool thing is, once you've turned it on now, the next time that I come in to power query then those that column distribution will be there. So remembers the steps from the last time.
Kind of like if you turn on the formula bar then it stays on from the last time right? So this is great.
This is cool.
You know it's not what the person wanted with the filters, but it will still give you an idea of the top 10 in the column.
In this case where there were only 1, 2, 3, 4, 5. Absolutely absolutely perfect.
Well, we are coming down to the end here.
I'm retiring from traveling to seminars.
We have three left out of state Chicago and then just outside of Appleton, WI and Chattanooga, TN.
Beyond those I'll still be doing seminars such as this one in Gainesville, FL.
Click that other link down there in the YouTube description how to come check that out.
If you like what you see here, please subscribe and ring the Bell.
Feel free to post any questions or comments down in the comment section below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Data Profiling in Power Query.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Why a great question came in today.
Someone was trying to use the Filters and said wouldn't it be cool if here next to each filter item in the drop-down menu a Count would appear so that we could see how many there were in each item and I said that is a good idea.
I bet someone UserVoice already has this and sure enough there's one out there but only 10 votes.
Let's see if we can get that up. That would be a great thing.
So down in the YouTube description I'll put a link to this this URL.
If you could go give us a vote that would be great.
But I said, You know, I know that there's a feature coming in Power Query. I just don't know if it's there yet, right?
So I wanted to insert table, make my data here into a table, and then on the data tab say from table or range.
You know, and at first like nothing looked Like there was anything new, right?
But I said Alright, where would they have put this? Not Transform, not Add Column.
Let's see what's out there on View and sure enough, its on View.
We now have this awesome new set of things.
First off, we can see things in Monospace in case you want to see if things are really lined up or not. So we have that one.
Column quality shows whether or not there's any error. So here everything is 100% valid.
No errors, no empty.
If there were errors then you can come in here and Keep Errors or Keep Duplicates or Remove Empty or Remove errors. So nice little bit of UI there.
And then column distribution. So I choose a column.
Actually I don't have to choose a column, they're going to show for all.
So here in Region I have three distinct and 0 unique.
In date, 440 distinct 340 unique. 7 distinct sectors. No unique customers.
27 distinct customers, 10 distinct quantities, which kind of shows that this data might be fake data, right?
And then the other one here?
Let's take customer and we asked for the column profile.
And it will show us that there are 563 values there, 27 distinct values. The smallest item is ABC stores.
The largest is Walmart and when I hover there 66 rows for Exxon, 65 rows for Walmart or choose another column an I get to see those items right? I saw this in beta out of the Microsoft Summit.
I don't know a year ago two years ago and I kept hearing all this is coming right and I said, well, that'll be great when it gets there. But it's here, at least in Office Insiders.
It's here. So check it out.
I didn't get an email saying, hey, this is the greatest thing ever. Go check it out.
I just was hiding back there on the view tab.
Although the cool thing is, once you've turned it on now, the next time that I come in to power query then those that column distribution will be there. So remembers the steps from the last time.
Kind of like if you turn on the formula bar then it stays on from the last time right? So this is great.
This is cool.
You know it's not what the person wanted with the filters, but it will still give you an idea of the top 10 in the column.
In this case where there were only 1, 2, 3, 4, 5. Absolutely absolutely perfect.
Well, we are coming down to the end here.
I'm retiring from traveling to seminars.
We have three left out of state Chicago and then just outside of Appleton, WI and Chattanooga, TN.
Beyond those I'll still be doing seminars such as this one in Gainesville, FL.
Click that other link down there in the YouTube description how to come check that out.
If you like what you see here, please subscribe and ring the Bell.
Feel free to post any questions or comments down in the comment section below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
