Self Service AI in Power BI Desktop | Community Webinar

>>Hi all. Good morning
or good evening, depending on where you’re located. Thank you for joining our
Power BI webinar, and my name is Deepak Shankar. I’m a community manager
within the Microsoft Business Application group. Today, we have Markus,
an MVP with with us, who will walk through the new
Power BI features and the self-service AI
in Power BI desktop. Hey, Markus, welcome to our
webinar, then it’s all yours now.>>Thank you, Deepak. Hi everybody. I’m really happy that you
joined my webinar today. So let’s go into the title,
it’s about AI, Artificial Intelligence in Power BI Desktop. So everything I will show
you will be available in Power BI Desktop, the free tool. So there’s no fees attached. One slide except this at
the very end that we’ll see something about Cognitive
Services and machine learning services, pay
your new subscription. But we’ve also got subscriptions
available for free. So if you just want to play around,
everything’s for free here. So for the agenda, I
chose a lot of stuff. So the agenda is
full-packed with Q&A, Quick Insights, WhatIf parameter,
Analytic Line, R Integration, Cognitive Services,
Azure Machine Learning. The definition of AI for this
is not a very scientific definition, but what I think
AI is everything which helps me from the tool. So everything Power BI
can make my work easier, this is considered as AI here. So we will see some
auto-magical stuff. So Power BI is transforming
stuff for you, discovering stuff for you
wit just a few clicks on the graphical user interface. Towards the end, we
also will see machine learning algorithms in place. Some of them, again, like
in cognitive services, are automatically available
so we don’t have to tune anything, but we will
also see more advanced stuff and in scripts like in
DAX, in M, and in R code. So I hope this session
will cover or have something for everybody
of you, no matter which level in Power BI you
are currently at. From that, we will go to
the demo and we will not that the demo until the
very end of this session. So if you have any
questions, please let Deepak know via the chat window
so he can interrupt me and I can answer any
questions you might have. Other than that,
let’s go to the demo. So this is Power BI Desktop
and I prepared it with my AdventureWorks thing here. So AdventureWorks
loaded into the model. The first demo is, wouldn’t
it be nice if we can just double-click on the pane and
then start asking a question? So for example, sales
amount and Power BI is capable of understanding
English text. As we can see here, the
text is underlined in red line and a blue line. This means it has discovered
a lot of things, it knows what I’m talking
about, and it showed me the 80 million of sales amount
all in the whole of my model. So Power BI understands
what I’m asking in English and it can has
just like by category. In the category, it’s suggesting
that’s the product category. So this is really nice. I want to keep this, so
I move this here and we can use this verse here’s the the
red line, here’s the red line. There’s also more here.
So we can also ask for a question like sales
amounts by promotion. Are my promotions we’re doing? We can transform this. So as long as this text
box is up there, we can change the question and
they can transform the Q&A into real visual. So this is nothing else,
does not distinguish itself from any other which I would
build through clicking on the fields or from
selecting visualization. What it can do, we can
prepare of course. So maybe I’m not
interested in no discounts because I’m only interested
in the promotion. So we can change this. You can see excludes. This is a real visualization. Another thing is, if you
ask for sales amount by country, I think it’s
territory, something like that, I’m not knowing my
own demo well enough obviously, territory group
or region, then it should discover there is like
regional data and it comes up with a map that
automatically suggests what probably I think is a good
visualization for it. I totally agree. If its territory information,
this could be put on a map or if it’s categorical data,
it should be on a bar chart. If I’m not satisfied with
that, we can also change this. So I can write as bar chart
and then we get a bar chart for the regions as well. Of course, if you ask for
sales amount by date, it knows that we have
continuous data, not the predicted date, I
chose the wrong one. I had wanted to order date here. It knows that it is continuous
data and it chooses automatically the line chart. So this is really, really cool. When I first saw the
people being with acuity, features of the art
this is cool for demos. But who would use it? I discover myself that I’m
really, really often used this because double
clicking the pane and then entering the fields and
categories I’m looking for is much, much faster for me
than finding the stuff in the fields of visualization pane. So we can continue with
the sales amount by date. What we are going to
do here is I transform it here and then will
use half of the space. When we take a look on
this sales amount by date, we can see that
there’s a peak here in January 1st that this is
where we hit the 4.2 million of sales amount. Again, wouldn’t it be nice
if Power BI could tell me what was special with this
date so we can find out why there’s the peak there,
so we can learn from that? Exactly that is what we can do. We can right-click here,
then we have to analyze, explain the increase functionality. If I zoom in here. Right-click, “Analyze,”
“Explain the increase.” If we click this, then probably,
it starts thinking. It’s explaining here. Here’s the analysis of the
58 percent increase in sales amount between December
1st and January 1st. Then it’s coming up
with different things. For example, it came up with
that the increase in sales. So there is different things
here I can scroll down there, and what it’s really
the most important stuff is like on the top. What we see here is, it’s
suggesting the increase was because the gender
of our employees, so the male salesperson sold
more than the females. This can be interesting
or not depending on how many male and female sales
person we have at all. So maybe this is not so
interesting, but for example, we can change it here. We can change this to 100
percent column chart. Then we can see that in
December, actually, the female salesperson hit
almost 1.4 million of sales, which is more than half,
52 percent, while the male only hit 47 percent and
this changed to December. So the males overtook the
majority of the sales. This can be interesting a lot. Again, you can then give
thumbs up or thumbs down to motivate Power BI coming
up with more instance like there. You can even press
the “Plus sign” here. So if you press the
“Plus sign” here, the visualization is taking over. So we can easily share
this insight we just got from the tool with all the others
by adding this to our reports. Again, this is a
normal visualization. There’s no difference.
So for example, we can change the colors on a
more conservative persons who maybe has the males and
blue and the females in red. To emphasize that the
important message of this column chart is that the
majority changed from one month to the other, we can
add a constant line at 0.5. So everybody sees that
the majority 50 percent was here and the majority
was for the females, and now changed to the males here. The next thing is getting a
little bit more interaction. So what I want to do here
is the following: I want to try out or play around
what’s happening if we would decrease the prices,
if we would give more discounts on our prices, so
we decreases the prices. Another we can do is
through modeling. We have then new parameter,
What-if, and I can give it a name. So this would be the
new discount, a decimal number, going from 0-1
and 1.0 increments, and tell the tool to add
a slicer to the page. So they get a slicer and it add a
table, generate a table for it. If we discounts on the prices. Probably the sales amount
will increase, but there’s no direct connection. There’s this idea of
elasticity of prices. So we add another parameter
four then new elasticity. Again, the decimal number from
0-5, and, again, in 1.0 steps. Funny enough, even if we select
Add slicer, no slicer is added. This is new, something went wrong. Let’s see what’s happening here. What it did create or
what it did not create. Yeah, it created a new
elasticity, but for some reason the slice is not created. This also happened in
previous demos to me. But no worries, we just
select this new elasticity and add a slicer for that. So we have slicer on our
own, and then we can, of course, select discounts. We can also change the discount
to be a percentage value. Maybe let’s do that. Modeling and let’s make
this a percentage, and then they change this
here, then they should see this as a percentage up there
and the elasticity it’s not a percentage. We pick it, put it to one now. So now the question is, how
does this change our sales? So what I’m adding here
is, we have under the Measures, I have my Sales Amount. I want to add Sales
Amount as Multi-card row. Just little bit smaller, and
then the Add New Measures. So we want to have,
what’s the price when we have the new thing here. So we’re going to go in
here and I have the tax here, so I have to
compute and to type it. So add a new measure here, and
this new measure is the new price. We can increase this. In this size everybody
can read this hopefully. So we have this new unit
price, which is the average of what the selected
old unit prices times 1 minus the discount amounts. If the discount amount is
24 percent, which is 0.24, the unit price is
multiplied by 1 minus 0.24, which is, that the calculate 73. So we have only the price
now on the 73, as what? 76 percentage. I should calculate this
with easier numbers. If the number is like 10 percent,
that’s what I can in my head. Then the new discount value would
be 0.1 and 1 minus 0.1 is 0.9. So we multiplied the price
by 0.9, so we get this discount of 10 percent.
That’s what I’m creating. Unit is measured, and I
add this measure to my Multi-card row. The next thing. As you can see here, I add
first the measures and then we can see the things here. So I add the new measure
here, and this second new measure is the sales amount. For the sales amount, it’s
not directly connected to the new discount value,
so the percentage, but it also depending on the elasticity. So elasticity of one tells
us if you decrease the price by 10 percent, then the
quantity will go up by 10 percent. It’s because you have this 1 plus
0.1 times 1, stays the same. So it’s like 1 multiplied the
quantities, multiplied by 1.1. If elasticity has two, it
means for every percentage we are decreasing the
prices, the amount is going up two percentages. That’s what you get
from the new quantity. Again, add this measure
and then I add the measure to my Multi-card row. We wanted to see how this
affects the sales amount. So the new sales amount,
new measure here. The new sales amount is
like the new price, the new quantity times
the new unit price. These two values we
calculate previously. We add this as well. Then for convenience reason, I
want to see what’s the difference. So if we do this, will
the sales go up or down compared to the old prices,
to the old setting on? So I calculate the
difference between the new sales amount and the
previous, the old sales amount, and we have this discount. Then we can play around.
So we can, for example, increase the discount
amount to 16 percent with an elasticity of one. This would not be good at you
because we would lose two million. But if the elasticity goes
up, then we can see that the quantity is going up. Let’s make this a little bit smaller. So then I change the discount,
we can see that if I increase the discount,
the unit price goes down. If I increase the discount
we can see then that the quantity is going up, so
274,299 now.If I increase elasticity, the unit price
will be the same, but the quantity is going up, and that’s
how we can influence this. What is the elasticity of
your prices of your goods? You have to find out, this
is an economic concept, and one way would be to
ask your customers or potential customers, how much
would you buy for this price? How much you will put
to buy for this price? Of course, this concept
has its limitation. So even if a liter of milk
or a gallon of milk is only costing one cent, you
would not drink, I don’t know, 20 liters or 10 gallons
at this certain limitations. For like high-priced goods,
there’s also limitation. If a luxury good would
like, I don’t know, Rolls Royce would decrease the
prices to really small amount, then of course,
ordinary people like us could buy Rolls Royce, a
car like this, but they would lose other customers
because they buy Rolls Royce, because it’s exclusive
and nobody else can afford it. So of course, there is
limitation to concept. Then we can see
there’s the ETL point. What we can do here is I
can create a line chart and put in the new
discount amount on it. The new discount here,
and I also put on at the difference, new sales amount
difference on the values. What you can see here,
there’s a really, really annoying line chart because
this line chart only has one data point. So it’s not a line chart, it’s
just one point here at 30 percent because this guy
here, the 30 percent is filtering everything on the
screen, and also filtering the line chart. So how can we get away
this, that we can get the older percentages and
it’s not filtering? We can go to Format and
Add Interactions, and I can tell this new discount amount
to not filter the line chart. Now, we get a full line
chart, and we see the curve, and somewhere there
would be the sweet spot. Of course, we can add
again a new analytic line. At the max position, I add a line
here and also add the data label. Unfortunately, we cannot
change the size of the data label, the data label is
usually is really, really tiny. So it tells us the sweet
spot, what we would have at this elasticity at 1.61. The sweet spot would be
to decrease the prices, so at the end have 4.6 million
sales more than before. But where is this part,
this discount point? You can try to find it with
the overhead to find a point here. Probably it’s here. It’s a little bit lower. You can play around them or we can
again facilitate back support. So I have two tax measures here. I first create a measures
and then I discuss what’s going there, what’s happening
in this tax measures. So the first one is the New
Max Sales Amount difference. What this is doing is, it’s
going through this table we created with the
What-if parameter. What-if parameter created
the table for us having values from 0-1 in steps
by 0.01, like the thing I filled out previously. It’s going over all those
values and trying out on calculating the New Sales Amount
difference to this guy here. Then for all those values
to be 100 values from 0-1, it will come back
with a maximum thing. So MAXX is an iterator and
it’s going through all the values and returns the maximum. So what we get returned from
this thing is the 4.6 million. Let’s see if this is true,
and click in there, and then they create again a Multi-row
card, and I’m adding Multi-row card here,
and then adding the New Sales Max thing here,
the 4.6 million. It’s exactly the number. The next step would be
to find the discount percent, where the calculation
goes exactly to 4.6 million. So we have the Maximum
Sales Amount, so we find out what’s the
discount for that. We can do this with tax. Little bit longer thing here,
I really can’t create it and build the scarcity on the screen. What you’re seeing is it’s
working with variables. If you’re not familiar with
variables, they’re called variables, but basically
it’s constant. So we are assigning a value
to it that this really cannot change throughout
the whole calculation. So I’m storing the new
max sales amount new difference, that’s the
thing which has calculated one step before into this
variable, and then I’m using the MINX to filter
out all the discounts where the new sales amount different
is the maximal thing here. So we’re finding the
point, the discount amount where this is exactly the
value, and we’re returning the discount to the percentage
for that, and if there is a point where we
have more than one edel point, it’s really unlikely,
but it could happen. So in this case, I’m
returning the smaller of this discounts percentages. It could also be a max or
you could find another way of equaling out
the two values maybe. So returning the average
of those two for example. So if you do this, then you
get the full answer for our calculation. You can add the new best
discount amount here, and again, it’s like because
this little guy, he didn’t knew discount amount, it’s
filtering the other one. So again, we want to avoid
the interactions here. But I think it’s just one. So this is a little bit
tricky to find the right one. Is it this one or is it this one? It’s not this one. It should be this one. I made something, maybe
I have to refresh it, sometimes that’s the reason. Okay. I’ve got my fall back here. So here, it’s showing
the right thing. If we click into 1.6, I think
it was previously, then we have this sales amount
and the new discount amount would be the 19 percent. If we try to get this here,
the 19 percentage, then we will see that exactly
they reached this 4.5 which is the maximum there. So next thing what we can
do here is, for example, we can add a table having
the date there, and then having different
measures like the discount, the freight, and the
order quantity, and the standard cost, and the
sales, and the tax amount, the product costs, and so on. If you try to take a look
on this table, it’s really, really hard to see if the
correlation between those values. So the discount was just
zero, the freight was 12,000, that the discount amount
went up, the freight also went up. But then the discount
amount only went back to a third, but the freight went up again. So the thing is the correlation
between items there can we learn from the data? Just looking at the data,
all those columns, it’s really, really, really hard. But what we can do here
is I can take this table and copy it down there,
and then we make a different visualization for that. One way would be to
use like the corrplot, correlation plot visualization. If we use that, it’s calculating
the correlations for us and we can of course
zoom this out so we can see it easier, a little bit
better, let’s zoom in here. So what we see here is
that it’s blue dots. Some of them are darker,
some of them are really, really light, and you
have the legend here. So if it’s blue, there’s a positive
correlation between two items. If it’s moderate, it’s
a negative correlation. So we only have positive
correlations here. The dark or the hard to
tell, is it the hue or what was thing just come for that? So the more blue it is, the
higher the correlation will be. Of course, the correlation
between the discount amount and the discount amount is
one, that’s a blue dot in here. Between the discount amount
and the freight, you have seen this light
correlation, but it’s really a weak correlation
something like there. So we can use this, for example,
to find out the correlation. We can also duplicate the thing here. We could build the very
same thing with R, which makes it a little bit of more
flexibility if we can do that. So I want to copy and paste
this here, doesn’t work. Copy and paste, does not really work. I copy and paste the other one, yeah. Not copied. Probably, I copy
the other one four times now. So I can change this to an
R visualization, R script visualization, which is a
standard visualization. The corrplot visualization
is a custom visualization which I downloaded
from the marketplace. So there’s this dot-dot-dot here. Import a customization
visualization where we either need the binaries
of the visualization on your machine or you can
download it from a marketplace. The R visualization is
coming out of the box. What we can do here
is that we change the visualization and then we
get this little R script editor here, which tells
me that the following code to create a dataframe. So Power BI is creating
a dataframe for me. If you’re not familiar with
dataframe in R, basically that’s a table that is also
the data.table in R available. But if you’re used to
relational data pure tables and the like, then
dataframe is your friend. In these comments or
everything which has this # at the beginning is commented out. So it’s only a comment
telling me that this dataframe consists of all
the variables I have put in here on the values. We have the date, discount
amount, and so on and so forth. Power BI automatically
always only returns a unique data set, so there’ll
be distinct values. Then at the very end, paste
or type your code here and that’s what we’re
going to do here. So to get the corrplot, there’s
this library corrplot available. So in our behalf, I looked
it up back in October, November, over 15,000,
1-5-0-0-0 packages libraries available, which
is really interesting, but also overwhelming if
you’re starting with R because all those libraries have
different things implemented. So that’s a really good
thing that this is a quite active community, but it
also makes hard to start. So start with things you
are familiar with or start with things you find
in nice books or Stack Overflow, and start from
the others would be the best way I would say. We can then have the corrplot. So what I’m doing here,
the dataset is that I’m removing the first column
because the first column is the date, and I’m
not interested in any correlation between the
date and the other things, only between the other things. I only have the date here
to avoid that Power BI only returns one row
with the sum of discount amount and the sum of freight. I wanted to have several
values per day, that’s why I inserted the date
here, but I’m not interested in correlation here. Then we have the corrplot
factuality and I want to calculate the correlation
of the dataset. If I’m lucky, we should see
I’m not lucky, I have a typo here object dateset. Okay. I have dataset, where is it? Here. It’s not dateset. I was talking about the
date column, and then I typed in the dataset. So we have the very same thing here. So now, you’re asking
yourself why use the more complicated script here? Because the script is more flexible. But if you want, you could
also come up with the corrplot mixed functionality,
which I could not replicate with the custom visual. This is changing the
script a little bit. Again, I have a typo
here, corrplot mixed correlation with double r, I guess. Yes, here it is, and
then we zoom this out. So this has changed the
script a little bit by showing us, canceling here. I’m not using the full space
for the dots, but only the upper triangle for
the dots and the lower triangle for the actual numbers. So this light blue thing
showing the correlation between discount amount
and freight, is here expressed in the same color and a
smaller font like the one or 0.2. So that’s what I was guessing
here that it’s up there. So R could give you more
flexibility if you want to play around with it. The next thing I want to
show is, again, has the dates and has the sales amount
available, sales amount. You’ll see this several times,
that’s my preferred thing. We can also again make
a line short of that. Let me take a look on that. I have the impression that
there’s an upwards trend. This is really volatile,
so sales is going up and down all the time. But for me, it looks like
there’s an upwards trend and can we somehow prove that
there’s really an upwards trend? One easy way would,
again, our friend here, the analytic line, with the line
chart, we have several things. Not only that we have
seen the max and we have seen the constant
line previously, but we’ll also have the trend line. So I can add a trend line and
to show me, yes, across here. There’s an upwards trend over
those years, over the sales. Again, we can use this, but
it’s not very flexible. So this thing is not even
telling us how this trend line is calculated. I know it, because I tried it out. This is simple regression
line, but it’s not telling us that, and we cannot
change the formula or the parameter for this
regression line anyhow. What we can do instead
is, we can calculate the values on our own, or
from the Internet. That’s what I did here,
where is the correlation? Here we go. This is the sales amount linear. I will copy and paste this,
I will not type this in, and I will first show
you how this is looking, and then we will, I walk
you through the whole script, this text script here. So we can measure, new measure. I copy and paste this here. I did not invent this on my
own, but I took this from Daniil Maslyuk. He blogs here and on his
blog you also find in science, how he came up
with this formula, and what’s the part, and most
important thing I will show you in this webinar. So let’s calculate this,
and then add this new sales amount linear to our line chart. So we set the new sales
linear and then we add this to our line chart. Let’s up in here, now I want
to add this under the values. So what’s happening now
is we can see, yes, the formula is obviously the same
because the line is the very same. The only thing which
happened here is that, this DAX formula is not only
calculating, it’s for all the time or the time spectrum,
where are the existing values, but is calculating
for all the dates available in my date dimension. That’s because I’ve brought
the DAX reflect there. But now we have to prove
this is working and is the same, and we can get rid
of the DAX outside line, yeah. So let’s take a look on the DAX. So the first thing is,
again, we have variables, we have the known thing. So that’s the actual values and
both are the actual values. On the x-axis we have the
date, let’s put we also have at the line chart,
and on the y-axis, we have the sales amount. That’s the known date,
this is used to calculate now everything else. The variant, we see the
formula, the typical formula for simple linear regression. We have the intercept,
where the line is crossing the ordinate, the y-axis
and then for every value on the x-axis are dates. We have a slope, so this
is going, if slope is positive for every day,
we get more sales. This could also be negative,
that the slope to be negative, that for every
day the sales would go down if you have a negative trend. The question is now, where
is intercept and slope calculated? It’s up there. So if the intercept for
example, and this is no secret, this is the formula for
the linear regression line. You take the average of
all the y values and subtract the slope times the
average of all the x values. Average of all the y values
is again here, average of y, its the average x,
this is the formula not on the hint of the values. So average x it’s going
through all the known values and calculating the
y-value, and then averaging it. So again, we have to iterate
the function we have already seen and that’s
the average for the y. The slope is slightly more
complicated, it’s like a divide by again y- slope
which you calculate up there. So the number of the items
we have times the sum of x multiplied by y minus
the sum of x multiplied by the sum of y, and
so on and so forth. So that’s the known formula
for simple regression line and Daniil took the effort
and wrote this down in DAX. So with that known, we can now
change of course, the formula. So for example, if I do
not want to have to claim over the whole period,
but only from 2013 on, because it’s the most
current data I have in my adventure example here. So if I change this and add
a filter to our trend line, then we only get values
which are greater and equal than the 1st January of 2013. Let’s try how this looks like. I change to DAX formula,
I close the thing here, now it’s starting only there. If it’s on the starting
year, maybe we can only calculate it from 2013. So we don’t get it calculated
throughout the whole thing and of course we
can do this as well, because we can go to the formula
again and add the filter up there. So we do want also for
the known thing, not everything, but the all
selected only for dates, greater equal than 2013,
January 1st, it will see here. Of course we can do
this. The negative thing is unfortunately into
already written, we have the negative trend line. But don’t shoot the messenger,
that’s what the data says. So with facilitating for
example, DAX, and more flexible than having only
the trend line available. You can also calculate stuff
like that again via R script. So again, take the date
here and then take the sales amount, with [inaudible]
dates much faster. Just want to show you, this is also
possible without queue in date. I use R calculation here,
so now we can enter an R script down there. This is hiding here regression,
here it starts, and it’s going up there. You can, if you want to
remove all the comments. So this is not necessarily to
have all the comments there. Then, let’s run the script
so we can see what’s happening in there, and then
I will discuss the script. So the green, the
[inaudible] , and the green line you are seeing here,
is again the very same SalesAmount we’ve seen previously. The visualization is a little
bit different because I’m not using the line chart
visualization from here, but in our visualization which
is then utilizing the ggplot2 package, which
we’ll see in a minute. We have to train them again. So the information is
basically the same like we’ve seen here, the green line
is the actual value, the blue is the trend line
equated here in the script. So how is this done? I
think, reading the script is not that complicated. So I get, again, a
dataset from Power BI. I’m really am into
[inaudible] so I’m moving everything over to another
dataset with different name. I did this because I already
had a script and I didn’t want to change anything
in the script. So I changed the name of the
datasets,I’m copying stuff there. Then I’m renaming the column name. So in Power BI, they’re called
date and sales blank amount. I don’t want to have this
so I’m renaming this stuff. I used the function colnames
which is running the column names of the data frame
which is stated in SalesAmount. Then assigning, this is
the left assignment thing here, the OrderDate and
the SalesAmount there. So from now on, the
columns are not date, but OrderDate, and not SalesAmount
but sales without a blank amount. Then I’m omitting everything
where there’s no date available, so get
out with dirty data. I’m using the as.POSIXct
functionality from R to transform the date into
a form which most of the functions in R can work with. So we have to convert the date form
of the [inaudible] date format. I’m using the ggplot library. As I told you, there’s
over 15,000 libraries. You’ve seen colplot, now, you’re
seeing ggplot and scales. ggplot is doing the plotting
depending on [inaudible]. The name is gg for grammar
of graphics, so we’re building up a plot with a
certain grammar which is typically for ggplot2 which
has nothing to do with grammar in R or in other
business decisions. Two is a successor of the first one. So every weekend, the
author of the thing started over again and started
over with a new name, ggplot2, instead of
moving the older package. Scales are helping me to have
the scales in this format. So five million would be debt. The default in R is it’s
coming from a statistician and mathematician, difficult
word, from mathematics. They’re using the scientific format. So this would be, instead
of five million, it is formatted, would say, 5e06. So 5 times 10 to the potential
of 6. Yeah, that’s helping in. Then, we have ggplot.
I’m passing in the FactResellerSales, that’s
the date, the data frame I’m quoting from Power BI. On the x-axis, I want to
have the order date, on the y-axis, I want to
have the SalesAmount. Then, I’m using the third color
which is obviously green. You can also use names here. I’m increasing the font
size to 18 to make it more easy for you to read the
stuff on the screen. I’m introducing a continuous
scale on the y-axis going from zero to five
million with this labels comma, which is [inaudible]
package is helping me here. I don’t need any legend. It’s the labels here like
the SalesAmount here and the Order Date here that
is coming from this part. Then, I edit the trend line. So start smooth with the
LM, it’s a linear model which is a simple
linear regression line. For example, we can’t
set this on to true, then we get the confidence intervals. So we get like a belt
around the line showing us how sure it will be or
how sure the model is. So more sure here than
at the end, for example. That’s the confidence interval. Or we could also use local
regression equation. So this could be not a
straight line, but this could be a curved line. If this is better to your
data, depends on your data and your portal and what you
think about your portal model. So straightened per
se is not that bad. It’s easier to understand, but
of course, it’s not the precise. If this is better,
you have to find out. So how I’m doing time-wise? Twenty minutes to go. So I will show you, we’ll
jump over to this thing here. Then, I will open up
for Q&A, I guess. So we have in the model. I’m not sure if you’re
aware of AdventureWorks. [inaudible] AdventureWorks. But I was always curious. In the AdventureWorks, we
have this product table. This product table has an English
description of the products. I’m working with AdventureWorks
rather long now, and it was always, what
could I do with this column? I mean, of course, I can
count the words there, I can read it, but what
kind of insights could I come up with from this
English description thing? One is, of course, in the
visualization, we can use the WordCloud which is also
a custom visualization. Word Cloud 2.0.0, I’m using here. If you change this, we get
a really nice Word Cloud showing us depending on
how often a word comes in a description, the
bigger the word will be. Unfortunately, the
text is in English. In English are the most important
or the most often words. For example, the, or a,
or for, or end, or with, which is not really helping
me because I am more interested in terms like aluminum,
or riding, or bike, or frame. This is like noise to me. So can I remove those
words or stop words? This is what I came up here. So I have three different
solutions for that. One is the cognitive services. I use cognitive services
to get me the key phrases from the description. I put the cognitive service
up there, that’s the middle. If I change this to a Word
Cloud, we can see this is the R. So here we go.
There’s the Word Cloud. We have the very same
text, but now I got rid of those unimportant stuff. Now, I see that mountain, or
frame, or road, or aluminum. Still aluminum is important,
the most important words there. So this is one way. The other way is that I
use not only cognitive services, but also Azure
Machine Learning services. Again, extract the key phrases. The first one is similar, or
they’re very identical to cognitive services because
I used the built-in functionality in machine
learning services, which is obviously very identical to what
cognitive services also offers. The third thing was that
I used Azure Machine Learning services, but
came up with my own text, with my own text mining thing to
get rid of the unimportant stuff. So this is slightly different
from the other ones. But of course, the most
important words like mountain, mountain are
there and there, and they’re well-hidden in this thing. So how did I made up this? Now, we’re returning into something
which could cost you something. Everything else I showed
you, R, for example, is for free, Power BI
Desktop is totally for free. If you’re using those
Azure services, of course, Microsoft cannot give you,
this is like computing resources, and disk space, and
memory, not just for free. There are subscription
models available for free if you want to test it out. I think you can test this
out without any cost. But if you want to go
into production, then you have to carefully think
about that you’re; A, exposing data over the
Internet to services out there, and B, every time
you refresh your Power BI desktop file to services
activated, all the data you’re loading in are sent
over to the service and you’re paying as you go. So for example, for cognitive
services, I think for every 1,000 data rules
you’re sending over the cognitive services,
it’s about 80 cents. So it will not kill you if
you try this out, even with the not-for-free subscription. But in the long run, you
have to think about build a model, maybe building
up a data warehouse and only sending the new data
over to the service. So how did I build this? I’m going to Power Query. So Power Query helped
me in building this. So for cognitive services,
what we see here, I came up with the following things. So first thing is, I came
up with two parameters, so the API key and the endpoint
from cognitive services. I did my input directly into my
code but with the parameter. So in case I have to change
the API key, because it always happens to me
that I give away my file. So if you need these files
to play around, I will gladly give you the array
so you can play around. But sometimes, I forget
about changing the API key and then I’m giving
array not only the demo file, but also my key to
the cognitive services, and then you can use
cognitive services on my cost, and then I have to change this. It’s much easier if it’s
just one space here, the API key and the endpoint. Then I used Power Query
functions to have a layer between my data and the functional
different cognitive services. So cognitive services
has plenty of services. I only have functions for
three of them, and this is only a part of the text-related
cognitive services, thus I don’t know how many services
out there. Really, really cool. So what I could show
you was the Key Phrases API, which is a functionality
here, and there’s also languages, sentiment, and I
also translated the data. So sending over the English
text and return the German text as I’ve
set it in Austria. So I can see, is this working or not? At the end, this is the
dataset I did show you, product cognitive services,
where I had the text containing all the stop words. I sent over all the roles
to cognitive services intended to return to
me the key phrases. So this is what we see here. So cognitive services and several
of those functionalities. You can see here,
there’s a product key. Can you see this? I
will zoom in here. So there’s like the product
key and the English product description. This is like the data from
my AdventureWorks data warehouse, and key phrases. This is the result when I
applied the Key Phrases function onto my English
product name description here. This is what it returned. So for most of the first
things, it just returned the very same thing but omitted
things like ML or HL, and made everything lowercase. If you scroll down where
there is more texts and it should show that the year
we get like this 4-1,000 time blah, blah, blah, it
extracted the Key Phrases. It’s blue, it’s a universal
fit, it’s a helmet, and so on and so forth. So how did I do this? I got their English Product
Name Description and then I said, “Add Column,” and
then I “Invoke Custom Column.” So I take this column, send
it over, give it a new name, Keyphrase 2,
and I use one of our functions, and I used
the Key Phrases API function, which I will
show you in a minute. Then there’s the English
product name and description. This is the name of the
column I want to send over to the functionality, to
the service, which then returns with the key phrase. So now we know that, how is
the key for the API build-up? This is a function which
I built in Power Query. Again, not really true. If you see the Advanced
Editor, I did not build it, and Microsoft does a really, really
good job in documenting stuff. So if you go to this link,
this is also available in the slide deck,
blah, blah, blah, cognitive services. You will see this code and you
could just copy and paste the code. I only did two changes to the
code from the webservice. It did work, but I want
it to parameterize. So this is the name of
the step in Power Query. This is the name of my
Power Query parameter. So I didn’t want to change
it in the queries, but I have this parameter API key, and
the same is true for the endpoint. I made a generic endpoint
and then I just added the parameter for the key phrases
or for languages and sentiment. You still want to
change that, did you? Then we can use this. How do
you get cognitive services? That’s the last step. You go to your portal, At the,
you can create resources, for example, cognitive services. This is really easy
because you just click on “Cognitive services,” create
the cognitive service, just create it or
just select it here. I think I come back here
and I press “Plus create a resource,” and I create
a cognitive service. This is really simple cognitive
services, here we go. Then I have to test in the name. It’s explaining what this is
about, as I told you, it’s not only text, but it’s also
a visual and audio, and read my services available here. After I give it a name, so this
would be PowerBIAI Webcast. I use my subscription. The location should be, of course,
close to where my data lives. So usually, I think, I use
the North Europe thing because I’m hosted in North Europe. Pricing tier and the
resource group, if it’s not existing already, you
can create it from here. Yes, of course, we
understand everything here. “Create”. Within a couple
of seconds, less than a minute, we have a new
cognitive service, which will then show you the API
key and the URL you’re using, then here for the
API key and endpoint, and then all your services
are up and running. It’s complete. This is really good. Then we get, I think, if
I go here and so get to “Resource,” that’s for sure the step. Then the newly created
web services shows me the key and the endpoint. These two things, of
course, you can write this down, but I will drop and
[inaudible] this service afterwards. So how much time left?
Ten minutes left. So I can also show you the other two. So the other one would be the
Azure Machine Learning Services. So cognitive services
is really cool because, just to show you, it’s
really easy to set up. You don’t need any
knowledge, you need Azure subscription, which can
be for free, and then you follow the steps on the
documentation or in this webcast to pass in the
key and the endpoint. But on the other hand,
it’s not very flexible. So if you don’t like how
the key phrase is extracted or how the audio is detected
or how the sentiment analysis is done, then
you’re limited here. This service is trained
and improved by Microsoft, but you cannot influence it anyhow. If you need more flexibility, go
for Azure Machine Learning Studio. Azure Machine Learning
Studio offers you also to create a Cloud service in Azure,
but you can create it on your own. You see that I already have
one extra key phrases, and we see how this looks like. We can also click in there,
it will look the same. So if you’re familiar with,
for example, integration services, this looks
very, very similar. So we have these items
here and they’re connected, and this is
how the data flows. So this extract key phrases, we’re
now in the experimenting models. So I used the book reviews
from Amazon, which is a dataset available
over the samples here. So I think there’s a
book review from Amazon. You just drag and drop this here. So you add it to your experiment. Then you add the next thing,
so I added the metadata. So what did I do here? I only selected certain
columns of the project, ProductKey and the
EnglishProductName. Then I partitioned the
thing, not really. I think I just use this to
split it up to send one part or the same part first to
the extract key phrase from text, which is
another thing here. Extract key phrase from text. This, I believe, uses the
same binaries like the cognitive services. So now we’re having more
flexibility around, so we can clean up the data
which did the Power Query or any other program since
through for the API. Then I used the existing
data and add the new phrase. So we’ve returning three columns. If you’ve done this,
you just run this. If you’ve done it, you can
deploy this as a web service. This is what I already did. I do not want to start this here
because it takes a minute or so. I don’t want to wait. So
we get this web service. Again, in this web service,
you can click and from the web service you get,
again, an URL and a key which you then use in Power Query. So let’s go back to Power Query. I have the HTML example here. So again, we have a web
service API key and you have a request URI, and
that’s what I used here. So I have, again, my ProductKey
and EnglishProductName. This was the other thing. I have three functionalities here. For HTML, it’s a little
bit more tricky because R you have to convert the
date you want to send over into JSON then send
it over to the service and then get it back as a JSON
and convert it back to a column. On DAX, there’s a
documentation how you can do this with our R scripts, for
example, but this did not really work for me. So I asked my different
my dear friend, Gahad [inaudible] , and he
told me that user block out there since many years
explaining or having a script where it can exactly do this. So converting your thing,
the Azure thing into JSON and then send it over and back. So I didn’t touch any of this
code and this is really, really working for me. So I’m using these functionalities,
again, applying it to my data. So these key phrases
again was done by add columns, invoke functions,
and this time it was called Azure ML
service functionality. Called Azure ML. This one, yeah. The third
one was with R. So I created an experiment and
not using the built in extracting but having, so this is
the very same except this one. Execute R script. So we have an R script
here, which you came up with, and there I have all the steps I believed that are
important in this case. So I’m cleaning up the thing. I’m only used a stem words, I remove
numbers, I strip white spaces. I remove certain stop
words from a list. I got some word. Just build in into this
functionality, punctuation and so on and so forth
and have this there. Again, it can then run this,
deploy it with the web service and then use my
very own R script and reuse it in many different
cases, like in Power Query by calling the web service. So five minutes also left. So the question is, are
there any questions? Shall we open up for Q&A? Or I’ll just go to my slides
and show the last slides. Deepak, are there any questions? I cannot hear anybody so
I’ve rip up the thing here. So the first thing I
did show you was to Q&A feature, question and answers. You’re asking questions in
literal English language, and then Power BI for
writing with answers from the dataset and the data model
discovered several things. Here’s the link. If you want to
read more about the Q&A feature. The second thing was the
quick insights, where we asked the model, “Please
explain me why is the CLT mountain at certain
points the highest? Do you find anything here? Again, there are several
things that quick insights can discover, not only
in trends but also other things, and all the documentation
is out in this link. The WhatIf parameter was
to think where we build the example with the elasticity. So if we lower the crisis
at a certain elasticity, how will this impact our sales? Will the sales be more
or less? Analytic line. We have seen on several things. One was the line chart,
we’ll see the trend line, and we can also build
the analytic line with DAX or R custom code if you want. Gives them more flexibility. But then you have to
write and maintain code. Cognitive services
was at the very end. That’s the pre-trained
machine learning models. Some data scientists at
Microsoft trained those models so you can believe that
they are really, really good. But maybe in certain
circumstances, they do not fulfill what you would
expect from them. So here you see again how to get
the endpoints into the thing. If you want more flexibility,
then Azure Machine Learning is your friend. This is using self-trained
machine-learning models. So you can, but you also
have to then train the models and then convert
them to a web service, which you can then use
in the other things. Again, how to find the API
key and so on and so forth. R integration really, really cool. I did only show you how
to integrate R in visual, but you can also use
it as a source or transformation within Power Query. Here are the links, we
can learn from that. I didn’t have any time
for web example, which is really, really cool. You can connect to a web
page and then just type in samples, what you want
to take from this web page, and then Power Query
will find out how to get everything and fetch
everything from the web page. The similar thing is to
column by example, if you want to create a new
column in Power Query, so I’m not a calculated column
in DAX, but in Power Query, then you can just
type in what you want to have from these columns,
and Power Query tries to find out, which I think is also
like artificial intelligence. I don’t know how to
implement this but this is a really, really cool feature. Check this out if I
have time for that. Other than that is really, really good since two
years or three years now, they changed everything,
the look and feel there. So the translations are
way better now, and if you find a mark, you can
just pull request on Git and can make the
documentation even better. Are there any questions? Deepak, I think we just
went through in 60 minutes. So if you have further
questions, feel free to contact me, reach out to me. I’m glad to hear about that,
and I think we can also discuss everything on the blog post. You should have seen on
LinkedIn or Twitter, and I think this video will
also be put on YouTube. Please add comments there
and I will make sure that I can maintain and give you
the answers to your questions. If there are no questions,
I say thank you very much for attending this
webcast and see you soon. For example, next week, and
in three weeks, I’ll do another one about Power
BI data model and Power BI bugs. See you then. Bye bye.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *