Power BI Desktop September 2015 Update
0 (0 Likes / 0 Dislikes)
This video, we're going to
take a look at the new features
included in the Power BI Desktop
September 2015 Update.
We have a massive update
this month with 44 new features.
Yes, I said that correctly,
we actually have 44 new features
which, if you count the number
of working days that we've had
since our previous update
in August,
it comes down to something
like almost two new features
per working day.
So we've actually
been very busy.
In fact, we have
so many new features this month
that I had to split
my use of PowerPoint slide
with the feature list
into four different slides
this time around,
and I've grouped them
by the major feature areas
in the products.
So you'll see a lease for
Report Authoring Improvements,
another one
for Data Modeling Improvements
and two more lease
for data connectivity
and Data Transformation
capabilities.
So let us start by looking
at the Report
Authoring Improvements.
The first improvement
on the list
is the ability
to define report-level filters.
So, before this update,
users were able
to define filters
at the visualization
and page levels,
but it was not possible
to define a filter
that would apply
across all of the visualizations
in all of the pages
on your report.
So let's take a look
at report-level filters
in Power BI Desktop.
I've switched
to this existing report
that I have
with sales information,
and now you will notice
how we seen
the visualization span,
where they used to be
the page level filters.
We've also now added
report-level filters,
so you can drag
any fill within your fill list,
so let's say, for instance,
we'd like to drag the country
and you can define a filter here
that would apply
across all of your pages,
so in this case,
I'll just have one page
but if I were to exclude Brazil
from my report,
you'll see how it will
actually exclude Brazil
from all of
the different visualizations
within all of the pages
in my report.
All right. Great.
So let's go and take a look
at the second feature
within the Report Authoring
Improvements this month.
The second feature is drill up
and drill down
for columns and scatter charts.
So it is now possible to define
drill up
and drill down behaviors
for columns and scatter charts
within your reports.
These behaviors allow users
to enable the consumer of
a report to see aggregated data
and to be able to drill
into a detailed
breakdown of that data.
For instance,
let's say that you create
a sales by country column chart,
like the one that I have here
in Power BI Desktop.
So let's say that you have
total sales by country
and then you would like to
actually define
a drill down behavior
that when you click,
it will drill down
to the total sales by city.
So I have my country
fill here in the axis,
in the axis area
and I can also drag city
and I can put it below country,
so now I have double axis here
with country and city,
two different fields
in the axis, in the X axis.
And now I can see how,
on the top of my visualization,
I got three new controls.
One is for drill up,
which of course, right now,
it doesn't, it's not enabled
because
I'm in the top level visual.
And then the second option
is the ability to drill
all to the next level,
so this will basically
take all of the data
that I have for sales
across
all of the different countries
and it will just break it down
by cities.
So if I do that,
it's going to
do total sales by city
and you see how,
now I get a bunch of cities
across all the countries,
so this is basically
all of our sales by city.
And then I also have the ability
now that I'm down to this level,
I could also drill up,
with the first control
that was disabled before,
so now that I've drilled up
and I'm back to the total sales
by country,
I'd be able to
enable the drill down mode
so when I click here,
on the top right,
I'll click to enable drill down
and you see
how the arrow now is selected,
so that means
that drill down mode is enabled.
And so, for instance,
now if I click on USA...
I'm drilling down
to see the total sales by city
within the United States.
Note that in this mode,
it's also possible
to still do cross filter,
so, for instance,
I could select year 1997
and I would do the cross filter
across all of the other charts,
including the one in
which I'm drilled down
and that, we've got the same
across all level,
so if I go, again, one level up,
it's going to still show me
the cross filter state,
based on the year
that I selected
in the other column chart.
Great, so let's go back
to the list of features.
The third new feature
is actually
two different features in one.
We have added
the different options
for page sizes
and page view options
for your report.
So let's take a look
at that feature
in Power BI Desktop.
So the first one
that we're gonna
look at is page sizes
which is available
from the ribbon and the report
or also if you select
the background of your page
and you go
into the formatting pane,
you're going to see section
for page size options,
where you have a few more.
So this page size
controls the size
and the aspect ratio
of each page in your report.
Keep in mind
that you can actually
a different page size to each
of your pages within the report,
so if I were
to create page two now,
I could assign
a different page size
or aspect ratio than the one
that I have in page one.
The available page sizes are
16/9, 4/3,
Dynamic selection,
Letter and Custom,
so, for instance, if we switch
these to Letter page size
and you see how my actual
report canvas readjusts
to the new page size
and then I can reorganize
everything here
so that it actually looks nice
in this page size.
I can switch it
then back to 16/9,
so I'm going back
to the default page size
that I have for my report,
but you get the idea, right?
It's fully flexible
because then you could
also have different sizes,
depending on,
for each of your pages
depending on the content
of your page.
Now the second group
of options is page view.
So with page view,
you can actually configure
how your report pages
should be render,
depending on the size
of the report viewer,
so whether
you're in Power BI Desktop
or in the Power BI service,
you'll be able to control
the different behavior.
So, for instance,
let's go and look at
the different page view options
which are also
here in the ribbon.
I'm able to specify
a fit-to-page behavior
which is the default
that I have right now,
or I can also say fit-to-width
and you see how now,
it's little bit wider
but it's also
a little bit taller,
so I get this scroll bar
and I can
then scroll up and down
to see the full contents
or I can also turn this
into the actual size,
which is going to make it
even much bigger in my view,
so I get also horizontal
and vertical scroll bars,
in this case.
Of course,
if I start doing things
like collapsing the ribbon
or collapsing some other things,
I'm going to be able to actually
see the full size, like now.
So just a few more options
for you to configure your report
and optimize it for the content
that you would like
to expose to your users
and make it
much more efficient for you
to create
these wonderful reports
with Power BI Desktop.
All right, so that's it
for the third options,
the third new feature
of page size and page views.
Let's go into the next group.
We're adding support
for inserting basic shapes
in the repot canvas.
So let's take a look at this.
Let's actually
make a little bit of room
for inserting some shapes,
I'll make this visualization
here a little bit smaller.
And now we've added,
in the ribbon,
the ability to insert shapes.
So we have support
for five different shapes,
rectangle, oval, line,
triangle and arrow.
So let's go and insert,
for instance, an oval.
And there it is and now
I can of course resize it
and then I get a few additional
format shape options
here on the side.
So, for instance, I could
control the color of the line
that's basically
surrounding this shape
or deleting the shape.
I can also
control the transparency
and the weight,
whether I would like
to have fill in the image or not
and if I do have a fill,
like I can also
control the fill color...
as well as the transparency.
I can also lock the aspect
so, let's say that
I want to lock it.
Now, if I can make it bigger,
it actually
keeps the same aspect ratio,
so it doesn't let me
make it more wider or taller
without making it proportional.
So what other options we have.
We have the ability
to rotate images,
also in shapes I've reinserted,
so you can set
either the specific value
or just use the slider
to actually rotate it
to the actual rotation level
that you'd like to have.
Finally, you can also, just like
with any other report element,
you could control the title
and the color of the title
and the position,
as well as the actual background
for the image,
or in this case,
for the abounding box
that contains the shape.
All right, so that gives
a few additional options
to format your reports.
And we're also looking
into adding additional support
for other types of shapes
in the future,
so if there's something here
that you're missing,
please let us know and we will
definitely iterate on that
and make
incremental improvements.
Now, let's move on
to the next feature on the list.
This is actually a couple of
improvements to the fields pane.
These are based on feedback
from many of you,
that navigation and being able
to find items within this pane
was a little bit
and I'll make one of my jokes,
it was a little bit painful
before.
So these two improvements are,
in first place,
a new search box,
so you can actually
find fields by name.
So let's say that
I'm here on the fields pane
and I would like to
find something about customers,
so I can now just start
typing something like customer
and it would narrow down
and highlight the tables
and fields that contain a match
for the search thing
that I'm pasting in.
Now, the second improvement
is the ability to expand
and collapse all of the items.
So before it was possible
to expand and collapse
individual tables.
Now, if you right click
in one of the tables,
you can also get access
to collapse all
and expand all,
so it really vastly improves
the usability
of this fields pane,
as you will see
as you start using it.
Now, the next item,
it's also improving
the interactivity
between the field well
and the fields pane,
so you can actually
drag multiple items
for the buckets
with the visualizations pane.
And for those buckets that
actually support a single item,
you're actually able to drag
a single item to replace it
when there is
already an item on it.
So let's take a look at it,
for example.
I'm going to select
the total sales
by category here.
And I'm going to go back
into the visualizations pane.
So you see how I have my axis
at the category name
and the Legend.
It's empty right now
so let actually drag something
to the Legend here.
Let's drag the category name
as well.
Okay, so now we got our Legend
over there with category name.
And so now,
this is a perfect example,
the Legend, it's a perfect
example of a field
that actually or a bucket
that actually only supports
one field at a time.
So if I drag a second field
like, for instance,
country to the Legend.
Before this was
not possible at all,
but now it actually replaces
the previous field
as you can see
and it populates it
with the actual country names
as the Legend
and they actually
updates the color,
of course, on my different
bar chart elements.
So before, you actually had to
remove the field
in order to be able
to drag it over there,
which, in some cases,
it would mean that the visual
would be
automatically deleted for you
if the bucket
that you were cleaning out
was the last one
that had an element on it.
So this greatly improves
usability of the interactivity
between the visualizations
and the fields pane
when it comes to configuring
the different buckets
for each of your visualizations.
Great.
Let's go back to the list.
We're on
to the second column now.
So we've added
additional formatting options
for data labels.
Let's go back
to one of my charts.
So let's select total sales
by country over here
and it's going to
the formatting pane.
So now if I go
into the data label section,
I have additional options like
the color of my data labels,
so let's say to something more
like red or pink, like that.
And you can also
adjust the display unit.
So in this case,
we're using automatic,
but I could also switch this
to the thousands so,
and you'll see that then we'll
go from millions to thousands,
as you see,
now it says K at the end,
or you could also configure this
to something else
like billions, maybe.
It's going to be too small.
So the default is auto
because we'll try and pick
the best option for you,
based on the actual values
in your data.
But, keep in mind that
you can always configure this
and we also provide an option
for adjusting the precision
or the number of decimal places,
your numbers,
in your data labels,
so let's say to three like that.
And you'll see how it
automatically outdates the data
in your data labels
in your visualization.
Great.
So the next item on the list
is some improvements
to the Cartesian Axis
in your charts.
So these are improvements
to the X and Y-Axis.
The first one,
it's about the label directions
for the X-Axis.
So we will
automatically adjust them
to be horizontal or diagonal,
so that the feed better
in the space
that the chart was given.
So, for instance,
in this example,
you see
how I had lots of countries
in this count chart.
Let's remove the shape
because it's getting on the way.
So you can see
how I got lots of countries
and some of these country names
are long enough
that they wouldn't fit
if they were
horizontally positioned,
just like the years
in this other chart,
they fit just nice and great
because
they're just small numbers
and there's very few of them.
So in this other one,
we actually automatically
turn them to diagonal,
so that they'll fit better
and be able to display your data
in a better, optimized layout.
The second item is the support
for Logarithmic
and Linear scales
for values in the Y-axis.
So you can actually
configure these
if you go to the formatting pane
and you select the chart,
of course.
You can go
to the formatting pane
and then go
into the Y-axis section.
So now, you have
a couple options for linear
and logarithmic scale type
for you Y-axis.
All right.
The next option is the ability
to set the hyperlinks
on textboxes
on top of a displayed text.
So basically,
before this update,
users had to
always have the hyperlink be--
basically the link
that gives the hyperlink,
it will have to be visible,
so if you were
to put www.powerbi.com,
you would actually see
www.powerbi.com
in your textbox.
Now, we've improved that
so that you can set
friendly text
or displayed text
for any hyperlinks.
So, for instance,
let's go and say more info
would be the friendly text
that I would like to have.
And now I can select this text.
And I can use
the insert link option
within the flying menu
and type in the url
that I would like to go,
so, for instance,
let's say powerbi.com.
And at the end I'll hit done.
And now this has been linked,
so it actually
when I mouse over it
then I try and click it,
it will bring up
this fly out menu
and here,
I can click the actual url,
that would
take me to that website
or I could remove the hyperlink
from here as well.
So it makes it
more efficient for you
to have some text
that it's descriptive
about what the hyperlink
is about,
but you don't have to
put the actual url
in front of the user
at that point.
Great.
The next group
of improvements are
improvements
to existing visualizations.
So now, we have made
a bunch of improvements
here across
different visualizations
and we have
different categories for them.
So let's just start by talking
about table and matrixes,
so we actually enabled
the ability to resize columns
within a table
or a matrix visualization.
On tree maps,
we actually support
now data labels.
On slicers, we support
the ability to select all
or unselect all
with a single click
within visualizer control.
And we also added
additional formatting options
for visualizers like the outline
and background,
as well as being able
to format the title
or the font color
and background of the header
and the rows, fonts for them.
So for a scatter chart
would be another one
that we've improved.
Now, you're able to configure
the bubble's color stroke
for cards, single cards,
we support
a bunch of formatting options
that were available before
for others
but not for single cards.
So, for instance, the ability
to configure data labels,
category labels,
the title, the background
and the word wrap options.
For combo charts,
we've actually improved
the performance so they will--
your combo charts
will render faster
and also
the interactivity of them.
You'll see that
they're more responsive
there's, there--
smaller or lower latency
when you click on them,
when you interact with them,
so they re-pane themselves.
And the last visualization
that we've improved
with this update is the gauge.
So you're now able
to customize the main marks
and target values
just within the field pane,
the visualizations pane,
you'll be able
to drag any fields
from your report to actually
become the main marks
and target values
for your gauge.
Now, the next item on the list
is the support
for displaying color saturation
values in tooltips.
So let's take a look at that.
So I have this combo chart
that we've been working with
and I'd like to add field
to the color saturation.
For instance,
I'd like to add
the total number of units sold
which is quantity
from all the details
of the color saturation
and you see
the color saturation now.
But what we've added now
in the tooltip
is that you will actually
see the value of the field
that will say
that's color saturation.
So now, you see quantity
as the additional value
with the tooltip over here,
the last value,
whereas, if I didn't have
the color saturation,
of course,
you gonna see that value,
you want to see the other two
that we're defining
in these visualizations.
So little improvement that
would make it easier for you
to understand your charts
as you look at them
and as you--
also where some of the columns
and bars on your charts.
Now, the last improvement
within the report authoring area
for this mass
is the ability to resize images
and to also apply
additional formatting options.
So let's go
and take a look at this image
that I have inserted over here.
If I select it, now I get this
new format shape pane
where I can configure things
like locking the aspect,
so if I unlock it,
I'm going to be able to freely
resize these however I want.
But if I lock the aspect, again,
I'm not gonna be able to do that
and I'll always
make it proportional
between the height
and the width.
Now, the next option
that we've added
for formatting shapes
is the ability
to define the scaling,
so whether you'd like
to have them normal
or fade or fill the space
that they were given,
so let's say fit, for instance.
You can also configure
whether you'd like
to have a title or not
and also you can set
background colors
and transparencies
for your images in your reports.
Great.
So on to the next
big feature area
where we've made major
improvements these months.
We're talking a little bit about
Data Modeling Improvements.
The first improvement
is actually a really significant
milestone for us,
in terms of data modeling
is calculated tables.
So what is a calculated table?
A calculated table is actually
like a calculated column,
so it is calculated
from other tables
and columns that
already existed in the model
and it actually takes up space
in the model
just like
the calculated column does.
So why are
calculating tables useful?
They are
a general purpose feature
and they actually kind of hold
any valid table DAX Expression.
So, for instance,
some of the common functions
that you'll be able to apply
by means of a calculated table
would be things like doing
a distinct or a cross join,
a union, inner join,
left outer join,
intersections
and even define calendar tables.
So you can actually
define those calculated tables
with those DAX Expressions
and they're gonna be
recalculated
when the model is reprocessed.
And then, so the columns
in the calculated tables,
they all have data types,
they have formatting
and you can apply categories
and default summarization,
so they're just
like any other table
in the model except that
they were calculated
based off of DAX Expression.
You can also, of course,
define relationships
between calculated tables
and any other tables
in your models.
So, well,
since calculated tables
are a part of the model,
they can actually be used,
among other things,
to simplify DAX formulas
where you can use
intermediate calculating tables.
You can also use them as a way
to speed up calculations
where the calculations
are already in the model.
You can use them
to create calendar tables
or even to partition a model app
with role-playing dimensions.
So you can actually
use a dimension
as a way of partitioning a table
into multiple tables
by using calculated tables.
So in addition to creating
permanent model tables,
calculated tables can also be
used to help you
with debugging DAX Expressions
that return tables.
So let's take a look at
how to create calculated tables
inside Power BI Desktop.
I'm going to
go into the Data View.
And here we have
the new table option.
So for instance, here now
that I have all the details
and I have my order ID column,
for instance,
so we could define,
for instance, order IDs
could be the name of my table.
And we could make it such that
this is equals
to distinct operation...
of order details.
Let's actually
find the column name.
So the other ID is going to be
equal to this thing or...
other details, other ID.
So insert the reference to that.
Sorry, having some hiccups here.
Okay, finally insert it,
sorry about that,
and then once it is entered,
this is actually going to
recalculate the entire table
and it's going to give me,
in this case,
it's a single column
which has the distinct values
for all of the order IDs.
So you can see how we can
start creating more complex
DAX calculations
on top of these calculated table
and we can really use it
to create any new table
that we'd like to have
based on any calculations
that you could perform
on top of your data model tables
or different columns
on different tables.
So very powerful feature
when you think about
the power of the DAX Expressions
that you can create
and how you can combine them
into many different ways
to again,
into new types of views
or partitions for your data.
Now, the next group
of improvements
that we've done in the modeling,
data modeling
area of the product this month
is within
the relationships view.
This view is great
for understanding
the relationships
across different tables
in your data model.
But up until now,
it was actually mostly a view
rather than a way of editing
or modifying
these relationships,
which was possible through
leave on buttons and so on
but it was not actually possible
within the diagram view.
So now you can do
three new things.
The first one is
create relationships
by drag and dropping
between two tables
in the diagram.
So, for instance, if I go here
into the relationships view
and actually let me demo first
the second feature
which is the ability
to delete a relationship
by clicking on--
hitting the delete key,
I'm gonna be able to...
Sorry, I'll delete a column
but actually
select the relationship
and either right click to delete
or hit the delete key,
so now I'm going to
delete the relationships
and that was
the second feature in the list.
Now, once we delete
the relationship,
I'm actually going to show you
how you can actually
create a new one.
So let's say that
I want to create a relationship
between the products table
and the order details table
because they both have
a product ID.
So I know that I can drag
the product ID from here
and drop it
into the products table
and it will automatically find
the right columns to relate,
in this case, by name,
it's an easy one, product ID.
But it also finds
the right cardinality for me,
so you see how it automatically
set the relationship
to be one to many
between products,
product ID and other product ID,
so that's all great.
The last new feature
within the relationships view
is the ability to rename
and delete tables and columns
which is kind of was,
what was getting
on the way for me
on the previous one
that I wanted to demo
because I was selecting a column
and I was trying to right click.
So now you can actually
delete a field from here
or you can rename the field
or you can hide it
so that it actually
doesn't show up
in the report view
in the fields list.
So if you had internal fields
that you wouldn't want to
share with the report author
and you would like to keep them
only on the data model layer,
this is a perfect
and a great way of doing it
within the relationships view.
Great, let's go
into the next group of features.
Within the Data View,
we've added
the ability to copy contents
from tables into the clipboard.
So let's go back
into the Data View
and let's pick a more
interesting table than this,
so, for instance,
let's pick other details.
So now I can right click
and I can select whether
to copy the entire table
or to copy a single column
that's--
let's go and select it
so if I copy the entire table
and then go into Excel
and paste,
this copy of the entire table
that I had in the view,
of course.
And, so as you can see,
it actually contents
lots of records
or lots or rows, in this case,
it will contain up to 2,155
which is exactly how many rows
I had in the entire table.
And you can also do this
with the specific column so,
for instance,
if I copy this unit price column
and I paste it somewhere else,
over here,
like, for instance,
in column Y,
I'm going to be able to
just paste that one column,
so full flexibility in terms
of what you can copy
out of the Data View
and into Excel
or into a text view
or even things
like Outlook or Word,
they will all work great.
We try to do the best job we can
in pasting these tables
into the right format
for the destination program
which you're pasting in,
so we hope that
this will be a really
valuable feature for you.
And the last group
of Data Modeling Improvements
is around field summarization.
The first one is actually
something that shows up
in the Data View,
sorry, in the report view,
so it actually lets you define
additional type of aggregations
or summarizations
within the fields pane.
So we added support for median,
standard deviation and variance.
So let's go back
to Power BI Desktop
and if I go into the report view
and I pick something
like total sales by year,
actually
let's do something else,
let's pick
a different field here.
Instead of total sales by year,
I'm going to do
the total number of units
sold by year.
So for that
I can pick the quantity
which the default aggregation
is sum,
as you can see
that's the default
for all of them.
But now,
I can go into this drop down
and in addition to sum, average,
min, max
and count that we had before.
We've added three new ones that,
for now, they're non-default
but you can actually customize
that to be the default,
so if you go back
to the Data View,
for instance,
and you select that same field
that I have here,
on other details,
that's the quantity.
You can actually see now
in the ribbon,
how there is a new drop down
for default summarization,
so the default is sum,
but I could
turn that into the max.
And now, if I go back
to the report view...
And I remove the sum of quantity
which was defined before
and I drag quantity again.
That's going to
default to the max, maximum,
so as you can see,
these are ways for you
to start enriching
your data model
with additional information,
so that the right
default behaviors happen
when you or someone else
starts offering the report
on top of that date model.
Great.
So let's go
into the last two categories
of improvements this month.
The first one
is data connectivity
where we have
ten different improvements.
The first one is support for
On-Premises Spark distributions.
So last month, we added
support for HDInsight Spark,
and this month
we're expanding that
to other Spark distributions.
So this new connector can be
found in the get data dialog,
under the other category.
So let's go
and take a look at it.
We can go under get data,
other and Spark
will be next to last over here.
And once you select it,
of course
it will ask you for the cluster
that you're trying to access
or the url
and you can also
select the protocol
that you'd like
to use to connect,
whether standard or http.
So after you click okay,
you will get prompt
for credentials.
And then you will be able
to navigate
the list of tables available
within the Spark server
and you'd be able
to load directly
or edit them
just as you would do
with any other data servers.
The second new feature
this month
is actually an improvement
on top of the existing
SharePoint list connector.
It used to be such that
the SharePoint list connector
would allow users to import data
from the SharePoint sites
but there used to be
a limitation
that only sites
where the site language
was set to English
would work with our connector.
This month,
we've improved the connector
so that we've removed
these restrictions,
so now users can get data
from any SharePoint list
in any site,
as long as it's SharePoint 2013
or newer,
regardless of the site language.
So for sites that are older,
such as SharePoint 2010 sites,
they continue to work
just like they did before
but they still expose
the same language limitation
because this improvement
has actually been done
only on newer versions
of the SharePoint API.
The next new feature on
the data connectivity this month
is an improvement
to the exchange connector.
So now,
we actually allow connections
to multiple mailboxes.
Users can provide
the email address
to connect to
a part of the source dialog
and then they'll be able
to specify credentials
for that source,
which you may actually use them
to match the same email address
that you're connecting to
or not,
so for those of you
who actually have a mailbox
that is being accessed
from many
different email addresses,
you could actually
configure that as well.
So this way,
you can actually start creating
or combining data
from multiple exchange mailboxes
into a single report which was
a very frequent request
from many of you
using the exchange connector.
The next feature
or improvement this month
on the data connectivity is
within the Excel Workbook
connector.
As you may already know,
we did perform tight detection
for columns
imported from XLS files before.
But we didn't do this
detection for XLS files.
So that's what
we're having this month,
we're having this
automatic column type detection
when you import XLS files
through
the Excel Workbook connector.
The next feature is the ability
to select related tables
when you connect
to a database source.
So very frequently, users
will import multiple tables
when they connect to a database,
but before this update,
these users
had to manually select
all the tables
that they wanted to input.
So it often
require understanding
with the underlying
databases schema would be
so they would actually pick
all the tables that are related.
Now, with this update,
we're having a new button
called select related tables
to the navigator dialog,
so let's take a look at it.
So I'm going to go connect to
one my recent database servers,
it's a SQL server database.
And now, once we get
into the navigator dialog,
you will see, this new button
shows up at the bottom,
so it initially is disabled
until you select
at least one table.
So, for instance,
let's go
and find my favorite database
which, as many of you know,
is Northwind.
And now, we're going to
go down to the tables areas,
so, for instance,
if I select something
like products, and then
I click selected related tables,
it will select all of the tables
that have a direct relationship
with products,
so in this case
it picked up categories,
order details and suppliers.
Now, if I click it again,
it will actually
select all of the tables
that have a direct relationship
to any of these four tables,
so that's going to
give me a few more.
In this case,
it actually just gave me
one more with orders,
but now if I click it again,
it's gonna pick up things
like customers,
for instance, and employees.
So ultimately if I were
to click these enough times,
I would actually
select the entire set of tables
within my database as long as
they have relationships
but it actually helps
once you pick
one or two key tables
that you know about and then
you click select related tables
so once you load this data
into the model,
you actually have all of
the tables that you needed
and they all have relationships
automatically detect it for you
and then it matches it for you
to author your reports.
And last thing I wanted to say
about select related tables
is that we've added support
for this capability
across many different databases
that we support
or database types
including SQL Server, SQL Azure,
Oracle, My SQL,
it's actually easier
if I show the list.
So we added support
for select related tables
for SQL Server, SQL Azure,
Oracle, DB2, MySQL, Sybase,
and Teradata.
Great.
The next new feature
or enhancement
to an existing feature
in this case
is enhancements to
the active directory connector.
So you can actually
use alternate credentials
when you connect,
so let's take a look at it.
If I go into get data
and I pick active directory.
Let's find it on the list,
active directory.
The first thing
that I'm going to be asked for
is the domain
that I would like to connect to.
So in this case
let's use something fake
like my custom domain
and click okay.
Now, the credentials prompt
would actually let me pick
whether I'd like to use
my current windows credentials
or I would like to use
alternate credentials
with username and password
for a domain account.
So this will actually would
make it much easier for me
to impersonate
as I write queries
on top of active directory.
The next new feature
or improvement is,
in this case,
an improved experience
when you connect
to a data source
and you actually import one
or multiple functions,
so as you do that now,
you'll start being
prompt automatically,
right after
you leave the navigator dialog
where you picked
those functions.
Either, whether you click load
or edit,
we will actually ask you first
for all of the parameters needed
to evaluate those functions
and then you will get
the output of those functions
into either the query editor
or the data model
and the report view,
depending on the option
that you picked.
So this is much easier
than it used to be
where before we actually
didn't prompt you
and you had to go back
into the query editor
to pick one of these functions
and then invoke it yourself
and then load it again.
So much, much more
simplified experience for you
to load functions
and to invoke functions
from data sources.
The next one is an improvement
on top of the import Excel
Workbook Contents feature
that we added last month
which we knew
was really successful
and really popular
amongst many of you
that are completing hundreds
and thousands of reports
with lots of different types
of report artifacts
from Excel Workbooks
into Power BI Desktop.
So this month, we're actually
enhancing that support
to also support
external connections
to Analysis Services
tabular models
that were defined in Excel.
So now this connections
will get combined
to AS live connections
in Power BI Desktop.
So they'll simplify
a lot of work for you,
so you don't need to
recreate those reports manually.
The last couple of options
that we've added this month
to data connectivity
improvements
are the ability
to delete all of the entries
in the data source
settings dialog.
So if you go into
the data source settings dialog,
so if you go through file,
options and settings
and go to data source settings,
now, you're going to find
next to the delete button,
you're going to find
the new dropdown
with which contains
the new delete all option,
which will clear up
all of the data source
settings stored for me.
So let's go back to the list.
The last option is,
the option
to enable relationship import
during refresh operations.
So this one actually,
this serves a little bit more
of explanation.
Basically, Power BI Desktop
automatically
creates relationships
between tables
when you load them,
if these relationships
existed in the data source.
For instance, if you were
connecting to a database
as we saw before
with the Northwind example,
you'd be able
to pick multiple tables
with relationship
and they will be automatically
recreated for you
in the local data modeling
in your Power BI Desktop report.
So in addition
to creating this relationships
during the initial log,
Power BI Desktop might also
create or delete relationships
between these tables
when you clicked refresh
because if the relationship was
gone from the database
or it was added,
we would actually refresh
the schema as well as the data
when you clicked refresh.
So this used to be
the default behavior
and it was actually the only
possible behavior before
because there was no way
to customize, to customize it.
With this update,
we're adding an option
which is available on the--
under the options dialog
so let's go take a look at it.
It's on the--
under current file...
it's under current file
data load.
So we have this new check box,
update relationships
when refreshing queries,
which would basically
let you configure
the default behavior
that you'd like to have.
So the default,
we've actually switched it
to not create or not delete
relationships on refresh,
that's based on the feedback
from some of you
that actually find it better to
actually lock down the schema,
based on their first time load,
results and then
don't have the relationships
coming or go away
as you refresh your data.
But if you
like the old behavior,
you can always
turn this check box back on
and it would recreate
what you had in previews
versions of the product.
Great.
So let's go into the last set
of improvements this month.
I know
it's quite a long list but--
so these are just
the last eight improvements.
Those last few improvements
all fall within
the Data Transformations
and Query Editor
areas of the product.
So the first one
is copied to clipboard.
We saw this bit earlier
in this video for the Data View.
We're also adding copy
to clipboard capabilities
on the Query Editor window.
So let's take a look at that.
We go into edit queries.
And within the Query Editor,
you're going to be able
to copy contents from a cell,
so if you right click on a cell,
you're going to find copy,
from a column,
so you can find copy
over there too.
If you select multiple columns,
you're also going to be
able to copy
or you could also do these
from the table context menu,
so you can copy
the entire table.
So let's do this, for example.
Let's copy
three different columns.
I'll copy them to the clipboard.
So now, keep in mind
that missing
the Query Editor window,
you only have a preview
or a subset of the data.
In this case,
it's giving me 999 rows,
999 plus because
we actually give you 1,000
but we never look
beyond that 1,000
so we don't know
if there is more data or not.
So now if you remember,
I copied this exact same table
before when I had them
on the data,
the Data View capabilities
for the same,
for copy to clipboard
and we actually had something
like 2,155 rows, I believe.
2155 was the number, right?
So that's because
we were copying
the entire set of contents
on that table
because that's what
we have within the Data View,
we have the entire set of rows.
So now if I paste what I copied
from the Query Editor
and I scroll down a little bit,
you'll see how we only copy
up to the first 1,000,
in this case,
because that's all we had.
So as you can see,
the data gets clipped at 1,000
because we only had
those top 1,000 rows
within the Query Editor preview
so keep that in mind
as you copy data.
If you want to have
entire data set,
you'd copy it from the Data View
which requires you
to load the data first.
If you have
the smaller subset of data
or you just wanted
to copy something
that was within the preview area
in the Query Editor,
you also have that capability.
The second new feature within
Data Transformations this month
is the ability
to filter date columns
by the earliest or latest date
and that's a dynamic filter.
So let's go back
to our Query Editor
and let's go to the others table
where they have
a few date columns.
So if I select older date
and I go into date time filters,
in addition
to all of the other filters
that we used to have,
now, we also have is earliest
and is latest.
So if I actually select
is latest,
it's going to apply filter
where first,
it will calculate
the latest date
within the current set,
and then it's going to apply
that filter on top of the data.
So as I said, this is actually
a dynamic filter
so if I refresh it again,
next year,
and we've got new rows of data
with newer older date,
it would actually filter
by the latest date
that it finds at that point.
All right.
So the next feature
similarly to earliest
and latest dates for filtering.
We've also added transformation
that would actually extract
the earliest or latest value
from a date
or date time column
which is available
from the transform tab
also here in the editor.
So let me actually
remove the filter step...
and show you again,
these are stated in the table
how you can select
that one column
and then go
into the transform tab
and now if you go within
the date menu or the time menu,
you're gonna find
these new options
for earliest and latest.
So if I select latest now,
this will return
as single scalar value
that actually is the latest date
that was found
within this column.
Great.
The next transformation
that we've improved this month
is replace values.
We've added the ability
to specify special characters
within the replace values dialog
so you can use them
to either find values
or replace values.
So let's go and find one
of the columns like customer ID
that's a text column,
so if you go now
into the replace values dialog
within the ribbon,
you're going to get
a few advanced options to apply.
So there's value to find
and value to replace with,
that's always been there.
And then if you go
to advance options,
match entire cell contents
used to be there as well,
but now we've added replace
using a special character,
so if you use that option
and you'd like
to insert a special character,
you'll actually
insert that character
in one of the two fields
above that contains
that has a focus at that point,
so if I switch
to replace with now
and I click carriage return
and line feed,
you see that it actually inserts
those two special characters.
So this was possible
before through custom editing
and specifying
the actually escape sequence
for that special character
but that was not
very discoverable
and not easy to know for you
which one
you actually had to use,
so this is much simple
now where you can
just point and click
to insert the right escape
sequence for replacing values.
Great.
On to the last
four improvements.
So the first one is the ability
to detect column types.
Any specific point in time
that you'd like to do
within your query.
So as you know,
Power BI Desktop
applies automatic column
type detection
when you import data
from certain data sources
like text files, web pages
and so on,
CSV files and so on.
So basically, any data source
that does not have any schema
or data type information,
we would actually compensate
and try to detect
the best column type for you,
so that's a one time detection
that we perform right
as you import data from that
data source for the first time,
but there's no way for you
or it didn't used to be
a way for you
to apply this end column
type detection
at any given table that
you have within the Query Editor
and so if you actually
wanted to set the type
for many different columns,
you actually had to go
and manually select them
and specify the type
that you wanted.
So now we've improved that
and we've given you the ability
to detect data types.
So, for instance,
if I select these three columns,
I have detect data type now
under the transform top
that when I click it,
it will actually
look at the rows of data
that we have and it would
detect automatically
the type that it was better
suited for these columns,
so in this case,
it actually detected
a couple of int64 type columns
or integrate columns,
number columns
and one of type text.
So these will make it
much easier for you
because it will save you
lots of time,
so you can just select
a bunch of different columns
and then click detect types
rather than
having to go one by one
and manually
set the types for you.
Great.
The last or the next
to last item on the list
is the ability
to refresh all previews,
to refresh all the Query Editor
previews with the single click.
So as you know,
as you may already know,
we have refresh preview that
would refresh a single preview
for one of your queries,
the one
that's currently in focus,
so as you can see now
just refresh
but if I wanted
to refresh all of them,
I would have to
switch between them
and click refresh one by one.
Now, I can just
go into this drop down
under refresh preview
and I have this
new refresh all option
that will go ahead
and refresh
all of the Query previews for me
within all the tables
that I'm having here.
So again, one of those things
that simplifies
and makes it
much more efficient for you
to work with
the Query Editor environment.
If you knew
you had to refresh all of them,
that's much easier for you to do
than going one by one.
Great.
And the last group
of new features,
well, actually they are
performance improvements,
those are always nice to have
and as you know, we continue
making performance improvements
across many different areas
of the product
with a renew update.
This month is the turn
for improving the performance
in these two areas.
The first one
is the choose columns dialog.
So if I go
into the choose columns dialog
from a very wide table
like, for instance,
my customers' table,
I believe,
actually products table
has more columns, so let's go
into the products table
that has 13 different columns.
I can go into choose columns
and the performance
on bringing up the list
within the choose columns dialog
is immediate, as you saw,
there was no noticeable lag
in bringing up this list,
and it's actually
much more evident
as you go with tables that have
up to hundreds of columns,
where you can get back
into this view
and immediately
start selecting the columns
that you'd like to keep without
having to wait for that preview.
Similarly to this,
we've also improved
the performance
on the list of values
to filter by,
so as you saw, there was
a little bit of loading time
but it was
just a few milliseconds
and now we're able to
more efficiently load the items
within the filter menu
as well as the expand
and aggregate menu,
so for instance, if I go
to a table like other details
that has this column
with nested tables
and nested records on them.
You saw how the load
of this menu was immediate.
So that's--
basically
the performance improvement
we've done this month,
it makes it
much more fluent for you
and much more efficient for you
to work with your data
as you apply filters
and aggregates
and expand operations.
Great.
So that's all for this month.
I hope you guys enjoyed
these new features.
I hope you find them
very useful.
As I was saying, this is very
massive improvement for us,
very massive update
with 44 new features
and we're actually
already working very hard
on the next round
of improvements.
So we think that October
will be also another great month
for Power BI Desktop,
so please stay tuned
for future updates
and in the mean time,
keep using our product,
keep sending us feedback
and we really pay lots of
attention to your feedback.
We are committed to getting back
to everyone of you
with any questions
or issues or suggestions
that you may have
and we look forward
to hearing back from you.
Thank you so much
for your attention.
Have a good one.