I'm going to cover the top five features in the Power BI
October 2018 update and number one would change your life at the end of this video.
You can also access the complete playlist with highlights for all the recent Power BI updates
Hey
I'm Avi Sing Power BI Pro and I help companies and individuals get quick and powerful
results using the agile method for Power BI now if you want to become a Power BI Pro,
make sure to hit subscribe and click that bell, so you are notified whenever I go live to answer your Power BI questions.
We're going to cover the five key highlights from the October
2018 update now number five is one of those hidden gems.
Which you may not be using often but really should but let's start with the life-altering
Number one now if there are any updates or changes that you are looking for in Power BI.
Make sure to post them in the comments below so we can all vote for them on the idea site.
So the number one mind blowing life-altering feature in October update is
Data profiling in the query editor. Let's take a look data profiling can prove invaluable
When you're connecting the new data sources
Which you may not know everything about which kind of happens all the time when you're starting to build a Power BI model
So here I'm gonna connect to a sequel server data set and again, I'm gonna pretend I don't know it. Well it's adventureworks
So I do but let's say this is a brand new data set and I have no idea about it
So here I'm gonna start with the adventureworks data warehouse. And again, this is pretty common scenario with warehouses
You're gonna see a long list of tables now. My number one advice would be that big data table
It may be called a fact table in a warehouse
So we're gonna pick fact internet sales and I'm gonna click this button to select the related tables now
I'm gonna select the related
tables and I'm gonna click Edit so I can show you data profiling in the query editor now data profiling you're gonna see it in
These bars right below the column header
So that's the first starting point, but I'm going to show you there's a lot more underneath there
So when you click on the table, you might see it kind of going through and kind of doing the profiling for you
But then it'll light up now green is generally good
You can see more details by going on the View menu and here you would click on column quality
So now if I look at column quality notice that for this one everything is green values are valid
There are no errors and there's nothing that's empty now
If you look at let's say beam product, you can see that there are no errors
But some of these values are empty in a lot of places and of course if you work with adventureworks, you know
That category is oddly category subcategories empty for a lot of products. So that's kind of an Arab
Well, yeah, that's that's something you might want to look at dig deeper into talk to whoever owns the data source
And of course if there are errors in case you're trying to convert it to a number, but not all
Values are numeric then these would show up here
So this is a great starting point
but that's just the first step now you can go so what I'm gonna do is I'm going to turn off the column quality and
I'm gonna turn on the other
Data profiling feature. We just call them distribution and boy, I mean if you would ask me which one is more valuable
It'd be hard to pick and I'll show you exactly why so calling distribution
First of all, you can see that product key
You can see that there are 606 distinct value and each one of them is unique
so they're not repeated at all as opposed to product alternate key which has founded four distinct values, but some of them are
Repeated so for this one can be a good validation for ah, this column is a key column
Which you can use to establish relationship now, of course
Adventureworks is simple. It's named key, but often in real-life scenarios
You don't really know you don't really know that is this column?
Unique it just a unique or not and the product the data profiling can now tell you that
Another place where this can be helpful is just trying to understand which are the more important columns
Now if you generally look at look-up tables kind of like product, you know
The who what where when how that I talked about so its customer territory
If you're thinking finance kind of the account hierarchy and so forth and generally which are the more important columns
I look for columns which are like, you know
Three to five values because usually they represent these high-level categories that everybody wants to look at data
starting out and then of course, there is a drill down beneath that so that's a pretty
Classic scenario and look-up tables you're looking for some kind of a hierarchy
So what you could start with is look at values
Which have just a kind of a few numbers and as you will see here once this stops spinning
Which is right now category is a great
One to start with so I would I would use this as a starting point and I would then
Actually look at the category and then look at try to look at fine the next level in there and again adventureworks is easy
but believe me knowing something like this can be invaluable and you trying to understand exactly what the hierarchy is and
Understanding the column the structure of the data
So that's everything that data profile can teach you right now, but my friends more is coming soon. In fact
If you do have a feature request that you've submitted ideas
Post them in the comments below so we can all vote for them as well
But yeah, keep an eye out for more in this area. Let's talk about number two
Controlling the data export options in your report and this can be critical. Let's take a look
So I'm going to show you where you can change the data export options
You would go to file options and setting options
And once that pops up you would go all the way to the bottom to report settings now if you see over here
This is the default export option and earlier. We didn't have any control over it
the only thing that was allowed was users were allowed to export both summarized and
Underlying data from the service and I'll show you exactly what each of them means. But now we have these options to only allow
Export of summarized data or just just stop that export and not allow it now. This is a file level option
I'll also talk about a global option
So let's go now
Of course these ones apply
To the power bi com service if you hand somebody the power bi desktop
And it's all it's got all the data imported
They already have access to the data anyway, so again this applies to the power bi com service
So let's go there and take a look
So I have uploaded
Three different versions of the same file just by changing those settings for each ones for this first part
this one is default and you can see here that if I
Let's try that again
If I go in here and say export data
I can choose summarized or underlying data and underlying data would give me give me more detail just to
Give you a look. This is what the data would look like
So the summarized data would essentially give you the data which is in the visual so individual
I have sales and budget by category and sales and budget by category. Well, there is a drill down there
so the subcategory is shown here as well and
Detailed data will show you the underlying data
And as you can see, this has product name product key level data, which is nowhere to be found on the visual, right?
So this gives you a lot more detail and again so you can understand that
Sometimes you would want the report users to have summarized data, but not be able to dig in to the data
Now this can happen in the scenarios even within your company where you're sharing these reports with everybody and just don't want them mucking around
in the detailed data, or
certainly, if you're a consultant and or providing a service to other users where you want them to be able to see look at the
Reports but not have access to the underlying data. So let's see how this differs
So the first one you saw the default it lets you export either one now. This one is no export
So notice here that the export is grayed out
There's no way they can click on that and the third option
Of course is if you only enable summarized data if the user clicks export the underlying data
Option is grayed out and cannot be selected, but they still have access to summarized data
So now you have this ability to choose this setting on a file level basis
Now what I would say is admins
Of course have an ability to go to the admin portal and turn this off entirely now, of course, this is a hammer
So use it carefully and and of course you have a little bit more control. You can enable it on security groups
So you have all the control at a global level and also at a file level now?
Let's talk about number three fuzzy matching when you're merging queries. Let's take a look now
all of the files that are shown in the videos
You can always download them by going to learn power bi comm slash download
So what we have here is a category table
Which has categories listed out accessories bikes were linked and some description there
Now we have our data coming in where the category is not an exact match. Now query editor you have to remember is case-sensitive
so this is not an exact match and
Cloth and bike isn't either and if you look at the merge right now, it is merging based on the category
But it is an exact match and we can see the results that it doesn't quite match
This one or this one. You can see it's returning null for the description. So now let's take a look at the fuzzy matching
We're gonna go back to the the queries dialog box
And here we have the check box that we can check use fuzzy matching now
There are more options here that you can set including threshold which goes from 0 to 1 ignore cases spaces and so forth
Right, but I'm just gonna you know, you know those for now just turn on fuzzy matching hit
Ok, and now let's look at the results. So great
So now you can see that this one where the case was different and this one where it was a bike
Versus bikes they both were able to match
so again try to use fuzzy matching where it works and it does have one option that I
Wanted to highlight was you can build your own transformation table
So if you have your own specific transformation, like om sft is Microsoft like which you know
The the fuzzy logic would never quite pick up, but you can supply that as your own list as well
So a pretty cool feature number four is really simple, but really useful search in filter cards
So if you had searched in slicers for a quite a while now
So you can see this is a slicer and it allows me to do a search
But now we also have search on the filter car. So there's a search box
So if you have a long list of things and you're trying to find them is gonna make it a little bit easier for you
Number 5 is the hidden gem that I talked about. So let's take a look. This is about explaining
Well, let me just show you so this is one of the features is that I don't use often
But I really should and maybe you're in the same space now
This feature has existed in power bi which is you can just right-click on
Many elements in the graph and just say explain the increase and power bi
Digs through your data and finds out those nuggets inside that hey
This is what's causing you the biggest impact an increase in whatever metric you're looking at sales are something else
And it gives you a lot of and as you scroll down you can see
different kind of insights that it is pulled through and it gives you
That starting point for looking for that needle in the haystack that what is causing that change. Why is this happening?
And you have lots of options in here
You can choose different visualizations. So really useful feature
But the key changed with this update is that now it works for additive as well as non additive measure
So here I have a sales which is a simple sum and additive measure
But you can have a distinct count
Which I have a product sold or an average purchasing price and you can still right click on it
And in this case, it's gonna explain the decrease and find out for me the key insights
contributing to the decrease
So those are the October highlights now my friend if you have a feature or change you would like to see in power bi
make sure to leave that in the comment below so we can all vote for that and
You can click here or down below in the description to access the complete playlist to get caught up on everything that has happened
with power bi monthly updates and if all of this leaves you feeling
Overwhelmed then I would remind you to focus on the 20% of the core concepts
They give you 80% of the results and my power bi tutorial is a great place to start with that
Until next time power on my friend
Hey keep watching more videos and deep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends