 All right, so hello everybody and welcome to the brand consort is Excel and Power BI webinar series This is the November 2018 edition we do this Every third Thursday of the month though. We are kind of late this time around But we have three webinars going on today. This is the first one Excel and Power BI webinar series which is going to run from 9 a.m. To 10 a.m. One hour Then from 11 to 12 we'll be having our financial modeling webinar So if you are someone that is interested in financial modeling Or the big models, so I think you should be able to join that one. They will also have another L and D webinar which runs from one to two p.m So today we have a very interesting topic to discuss today which is five cool ai features in excel and power BI so ai is something That is currently trending right now everywhere you go Go online every every time it's all about ai ai ai here and there so many balls around a high at moment So I'm going to be looking at five cool ai features that we have within excel and power bi So I'm your host for today I'm amage or a lower I'm a business intelligence analyst and trainer at the brand consulting I'm a power bi power courier and excel lover I love those products a lot and I'm also a Microsoft certified trainer and a Microsoft certified solution associate So today We are going to be looking at what is ai? two cool ai features in excel The intrigue cool ai features in power bi Then we'll also be looking at some of the new features that power bi released for The november edition so every month power bi they ship in new updates. So we'll be looking at some of the top new features That will ship out this november after All right So basically who would like to understand what ai is All about and uh, I'm going to be playing a short video for you to see before we now discuss About what ai is really about All right, so that video was A robot that was programmed by busting dynamics So ai simply means artificial intelligence, which is actually Giving computers the ability to think like human beings So computers are going to think like human beings are going to be doing Some of the things that we humans can do and probably They will even do it much faster and much more efficiently Than we currently do so we are all head of about the self-driving cars. That's one example of an ai So self-driving cars those cars are going to be driven without human intervention And they can actually hit the road and do everything that a human being would do on the road You avoid portals avoid obstructions avoid Things that you need to avoid just like a normal human being would do So ai is actually a field of computer science So when the scientists discovered that we human beings we need to be able to be much more productive So they were able to to develop computers and computers were actually helping us a lot to be much productive and do Much more that we could achieve normally as ordinary human beings And then they also now discover that okay computers are helping us a lot But then we still have a lot of human intervention for us to be able to use those computers So they came about discovering something else now, which we now know as artificial intelligence So now computers are going to be able to think by themselves like human beings Although they also have to learn first So they are going to learn how humans think first or how we do a lot of things for example The self-driving car is going to learn how a human being drives a car before it can now drive by itself So that involves ai is all about but before it can do all of those things It has to be able to learn from a real human being Soon as much as ai has a lot of benefits that can offer There are also some concerns about ai for example There's a theory by Elon Musk called the single lighted theory Which Elon Musk thinks that at some point we are going to be developing ai They are going to be much more intelligent than human beings and at that point Take for example, if a robot is meant to perform a task for example And it is much more efficient than a human can do and it thinks for example that Human beings are the obstructions to what is meant to achieve Elon Musk thinks those robots are going to destroy human Another actually scary example is when the facebook actually Programmed two robots and they asked those two robots to interact with one another and do some transactions With simple english, right? So they actually started out with simple english, but ended up developing their own language Which no human understood what they were saying. So facebook had to shut that down So that it doesn't cause some catastrophic or some unforeseen circumstances Soon as much as ai can help us a lot There are a lot of concerns that is all about but that is for a separate discussion Right. So today we are actually looking at some of the Cool ai features that we have within excel and power bi. So we'll be looking at two features in excel And now that three features inside power bi So the very first feature Is pixels in excel So speak cells is an ai feature that we have inside excel And this actually reminds me of my banking time where Tellers at that time used to do a lot of reconcilations At the end of the day, they have to balance out their account and if for adventure A teller is unable to balance out the account use we always see a lot of people guarding around that teller and trying to do What we usually call call over so call over is a process whereby someone is looking at the physical Uh vouchers that a teller has received and the other person is actually looking at the screen to call out All the figures of all those transactions. So someone is calling out transaction amounts and another person is cross checking manually to see that those transactions Correspond to what was posted into the banking system So speak cells is something that can actually help us do that So you don't actually need to have a second person calling out to you something you can do by yourself So excel is going to help you out In calling out all those transactions or anything you ask it to call out while you can go ahead and look at Your physical documents So i'm going to share my screen now Let me know when you see what i'm sharing So speak cells is actually within excel But for you to be able to call out speak cells you have to go to the quick access to uber So to get to quick access to uber i'm going to click on I'm going to have to click go to my options excel options file then go to demise akubah Then go to hands All commands i will scroll down and Add to Access to bar for adding the other one Cell i'm going to click for speak cells access My excel So transaction amounts i'm going to generate some random numbers some numbers from 100,000 500 thousand To use speak cells now all i highlight I like you i want excel to start calling out for me Then i'll go to my quick access to bar and click on this speak cells And excel is actually going to start to call out Everything i've highlighted so i can use my physical box check Without needing any other person's help 99.00 God's time 170,500 89.00 Okay, all right. So that is speak cells in excel and It's something that actually can save a lot of time and even much more accurate than human things because sometimes If you have to be reading out those figures yourself is actually very possible for you to see the wrong thing depending on What your mood is So the second AI feature we are going to be discussing is forecasting in excel So there is a new tool in excel that is called the forecasting tool which can actually look at what your historical numbers are And help you to project forward By using some machine learning algorithms So i'm going to be sharing my screen again to do a demo of this forecasting within excel So you can see my screen now. So this is a data set that has to do with monthly revenue from October 2014 All the way to march 2016 So i'm going to use the forecast tool in excel to see how excel can actually help us to forecast these figures forward Based on what this historical That we currently have here is If you use the forecast tool, you will have to highlight the whole area of the data that you want to forecast Then you go inside your data tab So you go inside your data tab you get to the data tab you are going to see towards the right You see a button for forecast sheets, right? So if I click on that button for forecast sheets I see it already has a chart for me that has forecasted these values forward from October 2014 to August 2016 So the blue lines in this chart here represent the actual figures and then the orange line represents the forecast figures Two lines forecast represents the lower confidence bound and the higher confidence bound of which What are the possibilities of what the forecast should be going forward? But we have a lot more functionality that we can use within this this forecast tool So if I click inside these options, I can change where the forecast ends Maybe I don't want it to end in august 2016. I want it to end maybe like August 2017 And also is also very good for me to change the forecast start period So instead of the forecast to kind of like starts At where my data ends where my actual data ends I could take this back a little bit backwards So I can actually have a sense of how the forecast is working say for example If I take the forecast back from March 2016 and I'm telling you to actually start from March 2015 It's going to give me a sense of how the forecast is working Is it really giving me values that are close to what my actual is So now I've changed it to March 2015 and You can see the forecast is now starting from March 2015 and is actually a little bit accurate If you see say like July 2015 What I have as my actual figures and what the forecast is predicting Is actually very close and the difference between the actual forecast And the lower confidence but a higher confidence bound is not that much So that's just to say the forecast tool is doing a very good job and the accuracy is not that bad So I also have the option to change the kind of charts that I want to see here So currently is displaying as a line charts. I can decide to show it as A column chart instead. So if I show it as a column chart, it's going to look like this But I think it's much more better if You use a line chart to display a time period data set So once you are done with all your settings like this, you just click on this create And it's going to create a separate sheet for us for the data that we are forecasting and also create a chart So if you look at this you see that The original data just has month and revenue So just month and revenue in the original data sets And it has created an additional column for the forecast revenue Remember the forecast is starting from March 2015 rather than Where the data ends at March 2016 Then the two other columns also created at the lower confidence bound and the upper confidence bound So without having to know any AI knowledge or Say machine learning algorithm knowledge or any complex mathematics you can actually Use the AI feature within excel to achieve some more productive work So that is our second AI feature within excel So we're now going to move into the features within power bi So I'm going to move on to the features within power bi, but I just chat in the comment box. What do you think about this forecast to the next cell What do you think about the forecast to the next cell you can chat in the chat area? So we have now covered the two features that we've elected for excel that is number one is We speak cells in excel number two Is it forecasting in excel so our number three AI feature? is Analyze feature in power bi So the analyze feature in power bi is something that helps us to look at our data and try to make sense of it So normally you have a data set that makes no meaning originally And then by the time you create some analysis with this and you create some chats Then that is going to give you much more meaning But even at that we still need to get more insight from that chat So I'm going to share my screen again For you to see this data set that I have and I'm going to try to use the analyze feature in power bi To see what insight I can get from that data set Okay So you have a data sets that is looking like this You have a data set that looks like this and we absolutely make no sense in the original form and You have to create some kind of analysis With this and probably put up some charts before you can make any meaning with these data sets So I'm going to go now into power bi and try to take this data set to power bi and create a quick chat Make more sense from the data Okay So I'm now going to connect to these data sets within power bi Get the data connected to excel Then I'll grab this Restore the car sales data 1.5 years Bring it into power bi So I'm connecting to these part sales, but I'm just going to Edit this Edit in power query to make sure all my columns are properly set Into the right data sets So everything is in power query My region markets called the affine period is a day physical period I'm going to have to change physical period from a date to a text This period is a text the place for rent then I have my revenue As decimal number not full number I'm going to change this to decimal number And my unit load is fine Then I will just come to this properties area and I will change this from fact sales to I'll just call it Sales Data And I'll close and apply So close and apply and I'll quickly pull up the quick charts of revenue by month Should I create a charts? I will create a column charts of revenue by Month So this is revenue by month and this actually makes Much more sense than the original data set that we have so we can see How our performance has been by month And if you now look at this you discover that Revenue has been doing well from between January up to June Being very well, but all of all of a sudden in july it drops to a certain Much more lower levels. So you might want to know why this job was really costing this job. Why do we have? Revenue dropping by a huge amount like this So for you to be able to do that, there is a feature point Partly that is going to go into your data set and try to see why has this drop happened since For this for you to get that I'm going to right click on this month of july When I click on july then you see An analyze option So within this analyze option because there they decrease between June and july is going to show me Explain the decrease but had it been I'm trying to analyze from a decreased position to an increased position Then I will see explain the increase. So I'll just select this explain the decrease and Part vi is running an analysis to see why we have this decrease. Can you see it's Actually bringing out The percentage of decrease. So here is the analysis of the physics and decrease in revenue and you can see Say service plan accounted for the majority of the decrease So parvia has gone into my data set and has actually created like a month or month difference between All my line of business And it has seen that service plan is the one accounting for the major difference So there are slight differences between past painter sale and copier sale But service plan is accounting for the major difference. So this is insights that you couldn't have seen ordinarily But with this AI feature in power bi It makes it easier for us to do much more and getting More insights so they are actually much more Insights from this as to the we have dropped So even looking at our stores, we say is this uh, Jalputa had the largest decrease among store So we can now see these stores. They are pulling up pulling down the revenue big gross between the month of june and july It's also looking at the market policy the performance Is on all the field The And If you need to see any of this chart within yours, all you have to Click on little to have a report adds to page. So that click on this Page It's going to add that to my Part bi charts so I can now see the we have this decrease between June and july I have another chart that is explaining that this would be right and service plan Is the major reason why we have that? That is the analyze feature that is our number three feature Number three ai feature in excel and power bi so we're going to move on to the next feature Which is the natural language query in power bi so without having any knowledge Of how to write formulas or how to create chart how to create reports You can actually ask power bi some questions about your data set and it's going to return for you The answers that you need I'm going to share my screen again. I will go to power bi and this time around I will be sharing On a blank power bi screen So let me know when you can see the screen as well So the natural language query Is giving you the opportunity for is giving us the opportunity to type in questions about our data For example, you might actually want to know Maybe which store is giving you the highest revenue or what is the revenue amount for the particular time period? And you don't need to write any formulas whatsoever All you need to do is type in there in simple English language And power bi is going to return the results as a chart So can you see my power bi screen? I've opened a blank canvas within power bi Let me see if you can see this So I want to show how you can use Power bi to ask questions about your data without writing any formulas So anyone can actually use power bi with this natural language query feature All you have to do is bring in your data and ask anything you like about your data set So I've shared a blank canvas within power bi and I want to see if I can get results I can get results from my data set without writing any formulas at all So if you look to the top of the power bi If you look at this my ribbon command in power bi you you are going to see on the home tab You see ask ask a question so you can ask a question From the data sets that you have Right another way to quickly ask the question is just to double click on an empty area within A blank canvas or on any canvas And you see this pop up ask a question about your data Okay, so for example, let me see I'll tell my data to show me Show me Revenue Are they show me revenue by month? Power bi is going to create a chart for me and show me Revenue and power bi is going to create this picture for me so now I can see what my revenue is By month without having to write any formula at all That it is super super cool feature. So anyone can get to use power bi But I can also chat in sending another request to power bi and say for example What is the Revenue amount For North Can you say north central What is the revenue amount for north central In April 20 What is the revenue amount for north scree 20 15 and power bi already gives me My results right so what is for example, uh, I don't know That's what I have in my field is revenue So I may want to say for example, what is What is the sales amount What is the sales amount As you can see one that having what is the sales amount power bi doesn't really understand what this is saying simply because I don't have any field within my data set that is called Sales amounts, but that can be corrected. All I need to do is to teach power bi That anyway you see revenue It is also called sales amount so for me to teach power bi to do that. I will simply go to My relationship view in power bi So I go to my relationship view in power bi. I will select this table. This is my original table Then I go to the modeling tab and you see this button for synonyms If I select this revenue field And I click on these synonyms Click on synonyms If you look to the right of the screen You are going to see That I have Another pane called the synonyms pane So I'll simply go to the revenue area And click inside a box and type sales amounts So power bi now knows that if I'm asking a question Using sales amounts. It simply means the same thing as revenue So if I now go back into my report view And I try to ask that question again What is sales amounts? You can see it's already giving me a figure. So what is the sales amount for? What is the sales amount for not centralized going to give me a result? So sometimes AI needs to learn From human beings. So this is just an example of how you need to train Your AI for it to be able to give you delivered kind of results that you want So simply because we don't have anything called revenue So if you ask it to tell you the sales amount is not going to understand that So you have to go in there and tell power bi that Revenue is also synonymous to sales amounts. So anytime you ask a question On sales amounts, you should understand that you are actually also talking about Revenue So that is another super cool feature Super cool air feature in power bi And that is our number four feature and we now go to the final one So that is all about the natural language query in power bi I'm going to move on to the final one because of time What is the final final one is narrative science in power bi So narrative science Is actually a company and they try to develop a lot of AI tools That's actually going to change the future of how we work So this is one tool they created as the first one to join power bi that is called the narrative visual So i'm going to have to import native visual into my power bi And try to see how that really helps us to analyze our data So i'm going to share my screen again Okay So power bi naturally has About 29 standard visuals So anytime you open a power bi desktop, you have those These 29 Visuals always there But you have the ability to bring in some custom visuals and one of the most powerful custom visuals actually Is one developed by native science for native for business intelligence We are going to import that custom visual now So for you to bring in a new custom visual We just simply come to the bottom of your visualizations pane where you have all those standard visuals So the last one there is the ArcGIS map Then you are going to see these three ellipses So if you just click on that three ellipses says import a custom visual So i'm going to try to import from marketplace So pick on import from marketplace and Wait for power bi to connect to me So we have a lot of custom visuals here that we can add to the standard ones And we do quite a lot of more interesting visualizations To tell better stories with your data So i will just simply type in the search area or type narrative Type narrative and click search So i will see narratives for business intelligence. This is the visual that I want in Microsoft Application best application app for 2017 So i'm going to click the add Click add to bring in that visual to my power bi So now I have it in power bi and I can actually make use of this now. So let's see what this does for us So i'm going to bring in this visual trying to create a report By for dimensions, I will say Bring in my dates So bring in my dates and I want you to display this by month So display this by month and then I will bring in Revenue as my values Then you are going to Have to click whether the kind of report you are creating is this grid or continuous for example in this Circumstance, I'm going to be using the continuous option not this case because The reports I have in this visual in this my report page is actually a continuous kind of reports not this grid So click on this write narrative And power bi that's actually already written a story for me based on this report So anyone even those that don't understand anything about reporting or data visualization can actually see clearly what this report is all about So it's actually writing a story and saying the average revenue was This and you can format this Just to look much better. So when I come to this formatting area I will go to narrative and tell it that the structure should not be paragraph I want the structure to appear as a bullet list So when it appears as a bullet list, it is much more readable So something else you also have control over what this is saying For example, if you see what is written in the first narrative, it says average revenue was 6.09 billion across all three periods So you have the part to change the language For example, we know that periods here simply means months So I'll simply come to this settings icon area within the visual Then under the first option, which is language I will go to month So month is currently showing as periods. I'm going to change that from period. I'll call it month And the plural is going to be Months The plural is going to be month So now my night is going to be much more Plaira So rather than show average revenue was 6.09 billion across all 12 periods I've not changed it to show average revenue Of course all 12 months then Some other things you can actually do with these is to probably go to let's say You go to characterizations On that revenue you might want to say revenue is money So it's going to show in dollars I guess If I say Money so it's going to be showing me in dollars the last is the defaults that it has Then you can actually go to Let me say analytics Or display So I go to display. I could say good change Good changes should show as green Then let bad changes show as red So it makes it easy For anyone looking at this report to quickly spotlight on important areas Where do we do good and where do we perform bad news? So let me know what you think about this humanity with your impact here It helps a lot To explain whatever you have inside your report page And that is the last Future that we are going to be showing today So I'll just quickly do a recap of all the features that we have looked at So the first one we look at speak sales in excel and how that can help you to quickly do Reconciliation without needing to have a third party The second one is forecasting in excel that is using the forecasting tool within excel to forecast Because forward based on historical Numbers then we look at the analyze between power bi which is very helpful when you are trying to analyze The reasons why you have certain increase or certain decrease inside your data Then we also look at natural language queries in power bi Which helps you to ask questions from your data without having any knowledge of writing formulas or whatsoever within power bi Anyone can actually use this And the final one we look at is the narrative science in power bi by looking at the narratives for business intelligence First form visual which one the Microsoft application This micro have for 2017 and that enables you to write a story about your data Without you typing in anything whatsoever So it simply brings out inside that even you are unable to see naturally So now want to see our top new features in power bi for november The first one is that you can now copy and paste between power bi which was previously unavailable And the second one is you can now expand and collapse metrics Go headers. So let me see if I can show how to do that Let's run and collapse metrics for metrics go headers in power bi So just quickly see If you can quickly show that it's not on collapse metrics go headers in power bi So I'm sharing my screen again Okay So let's say for example Here i'm creating reports using The metrics table visual I'll go back on this new page. So i'm creating reports of let me say I want to see region And I want to see also markets So both of them should be on rows Then I want to see that by You need souls So originally if you look at this You see that I have two things on my road. I have region and market on rows And what i'm currently seeing at moment is just the region So if I need to see I need to be able to expand and collapse this not central for example So enable me to see what are the triggers for all the markets within The not central so originally all you have to do before is to come and view down On all the levels by clicking on this last option expand all levels down So this is what's going to enable you to see everything at once, but this doesn't give you Your normal view and go to the formatting area go to where and On row headers, I think On the row headers This plus minus icons so that I put that on is 20 off so once I put it on Plus minus icons you see it show on the visual and from the normal view of the metrics visual Without having to view down all those in the hierarchy I can now just click on this plus sign here To expand and I will see the values for the market in north central You can collapse these and do that anytime I want to make it easier for you to Do interact with your reports That's an excellent feature and That brings us to the end of this showcase Excellent. Thank you very much Ahmed for the webinar and everybody. Thank you for joining us at the Brown consulting for another of our monthly webinars on excel and power bi So this was hosted by Ahmed or yellow who is also a business intelligence analyst at the brown consulting If you want to join us for all the webinars for the next webinar since you've registered for this one You're automatically going to get a notification for the next one Just remember it is the third Thursday of every month and third thursday from nine a.m To 10 a.m Nigerian time, which is west african time And we also have the financial modeling webinar from 11 a.m to 12 noon And it's training and talent development webinar at from 2 p.m 2 p.m to 3 p.m So also you could join us on office training hub, which is our e-learning platform And you get some free courses and quite a lot of other interesting courses on office training hub dot com You have an offer which you're seeing on your screen already If you just click on that offer and go at least watch the free videos of that training And if you're interested you could do them by the course which is report automation in modern excel So thank you very much. Ahmed for hosting and those excellent the features really look cool And hopefully more and more of you start using power bi, which is an excellent business intelligence tool Thank you. Ahmed And thank you everybody. That's the end of our webinar for this month If you're interested in financial modeling hang on in the next one hour and join us in the next one Hour or if you're interested in talent development join us in a couple of hours for the talent development webinar Thank you everybody and bye. Bye. This is the end of the webinar. We'll see you next month