 Good morning everybody and welcome to another webinar another D Brown consulting webinar on Excel and Power BI Happy New Year to everybody and we're going to have a really wonderful 2018 Politicians notwithstanding so today we're going to basically just be going through some Reviews of last year. I just want to pick one or two interesting things in Excel and Power BI last year And then we talk about it That's what we're going to do today. Typically, we do a review of the updates for Power BI in Every month Power BI does a lots of updates on Power BI and even Excel does lots of updates But we just want to look at some memorable ones from last year And so for those that haven't been watching I can see quite a number of people that we know on the webinars a person speaking is David David Brown One is my name I work as a principal consultant of D Brown consulting I've been working with Excel for a very long time and now with Power BI and Also a master trainer master instructional designer. So Sponsors for this webinar is D Brown consulting who basically experts in training consulting and payroll. So those are the three things we do and For those that follow all our webinars, you know, that there's also a financial modeling webinar Almost immediately after this one and in that financial modeling webinar today We're going to be talking about sensitivity analysis. So it's going to be quite an interesting one about sensitivity Analysis, which is done in Excel. So it's a bit like Excel, but it's more for financial modeling All right. So these are some of our courses. We offer. These are some of the in-house courses We offer we also have an online school on office training hub So if you go to office training hub dot com if you haven't already, there's a free Excel course there free Five-day Excel essentials course. So you could go and take that free course is very very useful on office training hub dot com Advice you go get that free course. These are all the other courses We do financial modeling Excel office financial analysis office 365 and the like so that is us You go check us out and we have affiliations with Microsoft financial modeling Institute and ATD Which is the association for talent development? So these are the bodies that we associate with financial modeling institutes for those that are interested in modeling I advise you watch the webinar starting at 11 o'clock today And there's a different link. Actually, it's not the same link. So if you're interested in it Just type a yes in your Chat and I will make sure you get to the invite once we finish with this webinar So if you're interested in financial modeling, you could just type a yes in the chat tool so that I know that I will ask someone to send you an email with the link so you can get registered for the next one Yep. So if you're interested just type a yes and you'll get that so For those really interested I think it's nice to announce that there's a new financial modeling Certification so there's a new just like icon or ACCA or CFA There's a new financial modeling certification from the financial modeling Institute level one is advanced financial modular level two Chartered financial modular and level three is master financial modular and I can tell you from being a modular for a lot of years That it's an excellent certification and guess what we've kind of put pressure on them and they have and they have of Convinced them that Nigeria is viable and they now have a center in Nigeria So the exams are going to hold in Nigeria starting in April So check that out go to fmi or fm Institute comm to check that out. All right, cool So, let's see what we have for today Well, another thing is you could join our meetup groups I know a lot of you here are from the meetup group and some of you are from the Pog group So these are all free resources. We have online. We have a financial modeling meetup Meetup.com we have the power bi and excel meetup also on meetup.com and then on power bi user group We have the Nigerian modern excel and power bi user group. So all those will give you emails You'll see our newsletters on that as well So that's all about us and all about the sponsors So we're going to get ready to enter into today's activity So if you want to reach us you could reach us on training at D Brown consulting net So if you didn't get any email from us regarding maybe the financial modeling meetup And you want some more information on some of our free resources Just send an email to training at D Brown consulting net or you could call us on 0700 Training and yes, of course, you could also go and get free courses on office training hub.com right, so today we're going to be talking about a quick overview of Live meetup dates. Okay. Well, we had some live meetups last year I will share some pictures with you and then we're going to talk about top five modern excel updates features for 2017 what our top five power bi features and Then we check and see custom visuals what custom visuals were really excellent for the year And maybe you have a challenge for the month and our winner All right, so that's the format for today So I'm going to jump into the excel side of things first And then we quickly go through to see what were the best things in power bi for the excel side is going to be a demo So we're going to use a demo to demonstrate these five things. I'm going to show you now So what are those five things? All right, so for us we kind of our favorite five I'm going to just show you the five now and then we go and do a demo so our favorite five is get and transform the get and transform in 2016 if you use excel 2016 they got rid of the get external data Tab and they just replaced it with get and transform if you've been watching all our webinars You see that we predicted that that will happen because get and transform is the modern get data And that is power query. So get and transform is just power query for those that Confused with the name get and transform is power query and it's a hugely hugely important tool in excel Power query is something they call an ETL. That is extract transform and load tool And it you can use it to get any Sources of different sources of data about 70 different sources of data and bring that into excel and do your analytics So it's excellent and another big one was two new functions switch and ifs So anyone here has used switched on ifs. Can you type yes or no in the chat? Have you used switch and ifs function so switch and ifs can type in the chat type? Yes, you have or no, you haven't type. Yes Anyone has has anyone use switch and has anyone use ifs not if not if the plural of if which is ifs Okay. Oh good. Someone has used ifs already. That's nice. Good. All right Excellent. Some of you have used ifs. This is a plural of if. Yeah, okay, and then switch Good. So that's we're gonna have a look at that. See a demo of that. And then another one we really like is flash fill Flash fill I think has been a hidden gem in excel. Many people still don't know About flash fill. They just see something happen. I wonder what is excel doing? So it's an old update, but I think it's still very cool And we have concat and text join. This is not concat in it. It's something called concat and text join And then finally three map visual. We really like this three map visual. That's all three map visual is a Visualization that helps you kind of replace other work. So we're looking at all these All these five things and then we can just use one case study to kind of look at all of them And then we go and look at what our top five for excel All right, so let's get started. All right Well before we get started, I would like you to quickly answer a typical poll that we usually do So we just have those statistics. So let's see. I just want to know What your current BI tool is? I know some of us we probably have updated our BI tool. So just just let me know what your current BI tool is All right, so it seems 86% of us Basically use Microsoft Power BI. That's excellent. I'm really surprised. That's nice. I'm 57% excel So it seems all our webinars started using Power BI a lot, which is very very good Okay, and Excel and Power BI work perfectly together What you can do for 2018 one of your new year resolutions should be how to make Excel and Power BI fully Integrated so that when you bring out stuff do stuff in Excel you publish it straight to Power BI and use Power BI to now Give you better visualizations because I think that's the best way to work You do some interesting analytics in Excel you publish it to Power BI then you now use Power BI to do visualizations, right? okay, so let's have a look at this we're going to look at switch ifs and To look at switch and ifs we need to kind of remind ourselves about if So if you remember if Let me just show if so let's let's start off with if first, right? so we're going to start off with if and Yeah, I know a lot of us use if but if you come if you do our modeling courses You see we always tell you don't use if it's inefficient especially when it comes to modeling You should use certain alternatives because nested ifs are very confusing So here we're trying to find let me just maximize this a bit. Maybe I'll just hide a few columns me hide this column Shortcut I just did for that is if you want to hide a column you click on the cell and if you want to hide this Column for example, you do control 0 Control 0 hides the column and then control shift 0 on hides it So you control 0 hide and then I highlight where the column is hidden see those two cells I know the column is hidden in between Then I do control shift as 0 and it brings it back So can you type? What is the commission for the first guy just type the commission? What's the commission so I know you understand what it looks like and then you can just type the commission And then we're going to do the formulas here. We are going to look at ifs So if we are using if this is the old-school way, right? If I said if if has this Argument that says although it's quite small. Let me just zoom in. Let me zoom zoom zoom in Okay Make it a little bigger for us. Okay. Okay, so equals to if If says logical test value if true value it falls So logical test is saying what is our logical test our logical test is saying this Sales channel, right? We're comparing this choice channel first. Are you equal to when you're equal to this sales channel at the top here, right? When you're equal to that then the table that I will need is This now V lookup is key for this solution But the only issue we look up is that we look up needs to know which table to look at If you if you everyone knows we look up, right? But we look up we only look at this table if it is online We only look at this table down here if it is direct and we only look at this table down here if it's retail So what you do here is I'm saying if this value is online then give me this table. I'm gonna lock the table Then I do a comma so the comma for if is if it is not Online what happens? So I want to ask another question. I'll say if this is is equal to direct Which are luck as well Then Give me this table. Yeah, so that's the value if it's online if it's direct Then the last question is no if it's not any of those if it is not this or is not this Then obviously is going to be this So the last one can we can just highlight that So, so let's look at this our formula. We're saying if this sales channel is equal to M5 Which is online then give me this selection if it is equal to M 12 then give me this selection if not give me what? The last selection now someone should tell me what's gonna happen when I click enter If you if you pretty most of you here pretty good in Excel Which is cool. So just tell me what will happen when I click enter. Let's see. What will happen? Just type enter and tell me what will happen Yeah, and it's really what we're saying here is hey if this is online then give me a table We selected a range Excel can't understand that so it's going to give you a value error. So, but let's see Enter let's enter enter To few arguments with let's just check what we did wrong here If this is equal to that hold on let me click on FX So I clicked on FX. So I saying if This is oh, I'm supposed to have typed if so you see we made a mistake. Oops. Oops. I'm so step another if So let's just do it again if This guy is equal to this heading have four We're saying give this is equal to this heading for give us this selection, right? F4 then comma was supposed to type another if we didn't do that and then if I'm already jump into ifs if this guy is equal to the next tables heading F4 then give us this range F4 But then comma if it's not if these two are not the same if this is not equal to this and it's not equal to direct Then what is it going to be equal to? It has to be equal to retail. So we just highlight retail So you can see that we have three tables three conditions when you have three conditions It means you need to ifs so the formula is the number of ifs you need is Equal to the total number of conditions you have minus one. That's the logic of if So the number of ifs you need is equal to the total number of conditions you have Minus one so I put two brackets if I enter this time around it's going to give us value error Now the reason is giving us value error is because there are too many things to show we highlighted this whole table So the question is if this is online. It's supposed to show us zero zero to one hundred two percent One thousand two percent now for to force a formula to show itself whenever ever you get a value error in Excel It means there's too many things there I want to force Excel to show me itself to show me so I press F2 Yes, but I want Excel to blow this formula up and let it show me really what is inside the formula Because value error means there are too many things and I can't show all so it's F9 when you do F2 you press F9 So you look at it F2 F9 look at what I have in here I have zero zero dash one thousand zero point two which is this can you see zero zero dash one thousand zero point two and Then one thousand and one comma one thousand and once dash two thousand zero point seven which is this So can you see that so can you see this with zero point two? So if this was let's say direct if I change this to direct and Then I come here and do F2 F9 You see that is now the zero dash zero dash one thousand zero point five, which is the second table So this is working But it's working but it's showing a range and the reason we wanted the range is so that we can now use V Lookup on that range. So for those that know we look up We will now come to the beginning of this formula and do your V. Lookup On your V. Lookup you check your V. Lookup. Yeah, what are we looking up? We are looking up the quantity So this quantity we're looking up We're now going to comma and then we're saying when you look up this quantity this entire table this if all it did was give us an entire table So for this entire table I want you to look at this quantity in this entire table that if is given us and then give us column 3 because column 3 is Where our commission is and then give us column 3 now most people when you do V Lookup the last thing is false, but this kind of V. Lookup You don't type any false at the end you just leave it blank and you close your bracket. So this is a special kind of V. Lookup So this formula is a special kind of formula and this is what works for commission Because what you're doing with V. Lookup is you're looking up the three different tables and V. Lookup can't look up to Three different tables. That's why you need an if so you need an if statement to go and choose the correct table Okay, so that's if let's look at Let's leave ifs there and let's do another one. Let's do ifs So let's see how ifs looks so I'm going to kind of minimize this. Let's just give us some space for ifs Let's take this side. All right. So I'm going to just bring out ifs So let's see how ifs works Yeah So if I'm too slow too fast, let me know please in the chat so that I can slow down or speed up Okay, so ifs ifs ifs. How will ifs do this magic trick? So let's let me insert some columns here. Okay, so ifs. Let's check ifs So ifs has what is ifs syntax? Let's check ifs is logical test one value If true, let's just put fx and let's see how ifs. This is how ifs works So if you remember if if had logical test value if true value if false But this one instead of you typing if and if if inside if you just use one if and do everything in one if So it's kind of similar to what we just did but the logical test is still the same thing this direct Are you equal to this heading here? All right. That is the first logical test All right. So that's our first logical test. Are you equal to that and then our value if true So if it is really true then give us this table then the second test We just go to the second test. We don't need to type another if and the second test is hey you are you equal to Are you equal to this second heading? f4 and if you are Then Let's give us the second table F4 So I'll stop there for now Let me just close this bracket and then if I enter again You're gonna get a value error because you're showing a range, but let me press F2 Let's just go into the formula. I want to go into formula without using the Formula bar. So what about? Here we're saying if ifs g5 are you equal to this if you are give me the range are you equal to Direct if you are give me the range if not that's at the end if not Just give me this final table. We don't really need to do that last test. So close the bracket and enter Okay, let's check FX let's check what's going on So checks whether one or more conditions are met and returns the value corresponding to the first true condition Oh, you need to do all of them. Unfortunately, you can't do just that you need to do everything So even the last test we need to do that last test direct are you equal to Are you equal to a retail? So this is a little bit tedious for and then we now highlight the last one But again, this is just one function instead of using two So if you have many many things like this to do many tables Conditional tables that you need to bring out is easier. So look at this formula This formula look at it. It's had if and two ifs this one just has one ifs and Then of course, we can go to the beginning of the formula and put that our V look up trick Say hey V look up. I want you to look up the quantity Then comma then at the end of my formula I want you to do the comma and give me the third column and then I don't put any comma false I just leave it that way and we get our 15% and both of them are working. So if this was retail Can see both of them change perfect? Nice. So that's ifs, but I think switch is better. So here. Let's try switch so switch What we do is equal to switch and Switches has a very subtle difference from ifs But look at what switch does now for those using power bi for those in power bi you know switch switches an Excellent tool in power bi in fact this is an example of Excel borrowing from power bi because most of the time I think 99% of the time power bi borrows from Excel and they keep on building power bi and every new feature you see in power bi You will see ah, we have this in Excel We have this in Excel But this is an example of something in power bi that Excel has now borrowed. So switches an excellent function Look at what it does. It says expression What is the expression then value one and result one? Right. So what expression are we looking at? so expression is This is our expression right this retail. This is what we're looking at retail So if it's retail that we want value one and result one basically means that for retail Okay, value one is online Okay Value one is online and the result one for online is the table for online Okay So here expression basically says like the thing we're testing we're testing this thing for everybody And your value one basically says hey, what is value one that we're going to compare to the expression? And then what result do I want by getting that value one? Right. So and then your value to your default or value two is still the same thing This direct right which I'll lock and then the result for value two is the table and Then your value three or defaults basically means since it's you only have the last one that can be the default We just highlight the table We do F4 now if you have any questions guys Just put your hands up and then I can slow down and explain maybe something you didn't get but if I click okay Again, we're gonna have a value error You already know why so I do F2 F9 and you'll see the Calculations there see that it's showing us the right table. It's showing us the correct table. So that's values So if you have a question before I continue, can you just ask question put your hand up? If you have any questions Good to continue Alright super so Maybe I'll give you a bonus because there's another very complex formula that could do this as well But this is we look up and then I said, okay, give me what switch gave me switches switching between various tables I think switches the most is the cleanest out of the three switch and then Sorry before we do switch. I want to look up the Quantity then I do a comma Then we do our switch and then give me column three and then I close my bracket So we look up works. So this is switch. This this is if this is if this is switch And they all work fine and then we can highlight this three and then double click So question, which of these three functions do you prefer? Can you type it in the chat? Do you prefer if ifs or switch? Okay, Solomon prefers switch Solomon Paul prefers switch Switch switch switch is winning everything. Why okay, let's even check the length of the formula Okay, let me if you let's see which formula is the longest So sometimes when I want to test formulas, I do long tests, right? So let me put all these formulas here. There's a function called formula text So formula text. Let's see what the if looks like. This is what the if looks like, right? If you look up So this is what the if looks like. Let's see what the ifs looks like and let's see what the switch Looks like. All right So I found what the if looks like let's see what the formula text So formula text what it does it just goes into a cell that has a formula and then extracts the formula and shows you what the formula is and Then formula text for Switch So if you look at it, you can see that this switch function is the shortest switch function is the shortest, right? So that's cool. So that's if ifs and switch now quickly. Let's look at two other new functions in 2017 concat and text join Interesting functions concat and text join now For concat and text join. Let me use another sheet. Let me use another sheet to explain concat and text join Now one thing with the concat and text join is if you want to I Think it's best to use it when you want to reduce your file size and send maybe one column So for example, look at this data. I would like to combine this entire data into Just one column by putting a delimiter of maybe a dash or a pipe pipe symbol is this. This is pipe Oh, sorry not here. This is pipe. I like using this pipe symbol So so what I want to do is this I want to accomplish something like this for this row of data Yeah, I want to do this. I want to make it show something like this two three two six two Then the pipe then the name of the person can this levy Yeah, then the pipe then Like that, you get what I mean. So I want to just put pipe pipe pipe. Now if I want to use con Teenage everybody knows concatenate. This is the old one Concatenate so do with concatenate. Look at what I have to do. I'll have to click the first item Right, then I come to the text to and put the pipe then I click the second. I the third text three Then put the pipe then text for You get what I mean, and then you continue doing that until you build this thing Now what concat? See concat concat is a new function and concat what it does is it just allows me to highlight all like this So once I highlight everything like that and I close my bracket and enter guess what it does It joins everything just look at that. Look at that. It joins it all together Now the only problem with this is what can you identify it? Can you tell me? So for the pipe symbol the pipe symbol is on your keyboard And you just check your keyboard. Okay, let me see usually to the right of shift to the right of shift Is the pipe symbol to the right of shift or you can use insert symbol if you can find it So, okay, so what is the problem with this? I don't have the pipe, isn't it? So since I don't have the pipe to get the pipe that that's where text join comes in text join is a far better symbol so look at what text join does So text join says what is your delimiter? So I'm going to put control a text join says What is your delimiter? My delimiter is the pipe ignore empty Let's see text one so text one. Well, let's see text one. Let's see if text join is smart enough So text one. Let's highlight all the text and let's just see what it gives us So once you do that I said my delimiter is the pipe and then ignore empty. Well, let's leave that True or false can leave that can say true ignore empty or false don't ignore empty and then click okay So what text join did is this? It just put pipe pipe pipe everywhere perfectly So this is far better than The concat Things a better approach than concat and what this can do is that you can now send this I'll save as text And then you save as text this you can now send it to somebody and someone can use text to Call them to break it into a table Very very small files. If you have a huge file, you could basically send it out like this. It reduces your file size So that's text join. Let me quickly do flash fill flash fill a lot of us I don't know how I've forgotten to use flash fill Flash fill if for example, I want to start here. I want to extract this start here Yeah, I want to extract start here. Which is um, these are all presidents in the united states I guess is our new president there. Yep. Donald trump is right there at the bottom of the list interesting, so um our start here I want it to be 1789 right But but I want to now feel all of that out down here And for you to do that if you you need to really know excel very well Maybe you use a delimiter you go and find out where this dash is and then pull it you do a lot of mid All sorts of formulas. You don't really need that. Let me just format this as general So so that it's not a number. Just simple general format. Now I do 1789 then I enter and I do what's the next one 1790 Can you see what's happening here flash fill is trying to be Smart, but it's not really doing is already Kind of guessing what it is we want So that's what flash fill does when you type one item and you must make sure you are next to Because we're trying to extract something from here. So you must go straight to the next column It has to be the next column not any four columns after the next column Then you type the first thing you want the moment you're typing the second thing you want Usually flash fill will just work And once it just works you just type enter you hit your enter key and it just fills it out Okay, so let me pretend that flash filled didn't work this time. So 1797 Okay, enter then I do 1801 And then I enter All right, now let's say flash fill didn't really Get it. So what do you do to help flash fill work? If you just click here, there's a shortcut control e Control e just fills it. So what I just did is control e. I was just clicking anywhere here and I did control e Control e will flash fill Okay, so you can write that down write that shortcut down is excellent shortcut control e flash fills Now if you forget control e What you can do is after putting an example for the flash fill come just click anywhere and then go to data Under data, you will see flash fill under data tools flash fill. Just click flash fill and it fills Okay, so that's how you do flash fill. There are many other Interesting things about flash fill, but really I have to jump straight into power point because we have to talk about power. I mean not power We have to talk about our power bi top five I know I haven't talked about three map. I'll quickly do that. But let's quickly look at our power bi top five So a power bi top five if you watched our webinar, I think for november And if you haven't please go and watch our webinar from november go to the youtube channel Type d brown consulting and you see bookmarks bookmarks. I think are extremely One of the best features that they brought in last year bookmark or bookmarking It's really really really cool And then english language queries came in in december so me. I think this is the also very very powerful For those that have power bi dot com English language query basically means in fact, let me demo it for you I'm going to open power bi because this is worth demoing for sure So let me show you english language queries Okay, I'm going to open power bi Just open a quick power bi All right. So what is english language queries? So if you look at my data I have data on Budget and data on sales and stuff from revenue for In particular, yeah So when you give an exec and an exec wants to ask questions about your data All they need to know is have an idea about what the data is then click anywhere in the canvas and power bi Then they can go to view. I mean go to a home They should see q&a in home under home under insert. You see Ask a question When you click ask a question This kind of box comes up Now what this is basically saying is waiting for you to ask a question in english So here I can say revenue By state All I did typed was revenue by state and guess what a chart just came out Revenue by state and a chart comes out That is how wonderful q&a is Whenever when you build your data model in power bi The first thing you should do is just q&a just be doing various q&a and q&a also is kind of mixed with Artificial intelligence through machine learning because it's a machine learning language or or engine running inside power bi That q&a help you make some detailed analytics Now one of the best visuals if I don't get to it is narrate. There's a narrate visual that helps you narrate What happened? So for example, your manager says, hey, what happened? What's the variance between last year and this year? Why was there a variance? You could ask this custom visual called narrative To give you a narrative of what happened and to tell you oh production went down by 20 percent because I don't know plant was down for Plant was down for 20 days It would go into your data and go and look for the reasons why something happened and then narrate it in english So that's a custom visual if you haven't used it before please go check it out So this visuals I just typed revenue by state. Let me say by state maybe on a map Let me see if that works on a map. I hope my internet works So look at this I said by state on a map and then you can see what is done It's just created a map of nigeria and showing me revenue by state on a map. So I remove on a map I'll say With tree map. Let me say with tree map three map Okay, can you see this is the tree map visual I talked about so this revenue by state with tree map So look at it revenue by state with tree map and I can expand the tree map And guess what my q&a has just given me a custom visualization beautiful custom visualization Since we talked about tree maps in excel. I'm going to take this data I'm going to take the data for this and then we're going to create a tree map in excel exactly like this So to do that I'm going to replicate this control c. Let me delete this to the left I really like tree maps. I think it's far better than pie charts So control c. I'm going to click here control v And then once I control v. I've created a new one. I'm going to change this to a table visual So I've changed this to a table visual here. This is a table visual I just wanted to grab the data and then let me see if we can Can you allow me to grab the data? In fact, do you know what what I could do? I could just copy So not allow me to grab. Let me see if I do If I change it to this one Control a I think we can just go to the back end and get the data So that can create a small map Where is revenue and state? Okay, so we have star name and state Let's say I just want the revenue figures copy I'm just picking the revenue figures and we're going back to excel So I go back to excel and let me just drop Data Power query Or do you know what I'm just going to quickly get some random data. Let's use let's use what we already have Let's use a state this country's right So I'll just use this country's and the unit sold. So let's do it a tree a tree a tree map So I'm going to do a tree map based on this data and This two data right here this data and this data Two of them I'm going to use this to create a tree map So let's quickly insert Under charts we now have if you click on the button for charts this corner button here There are some new chart types out. I'll just say all charts And we should have a tree map visual So I have a tree map visual and because there's so many states I think you have so many dots But this is tree map and then I click okay. So what I did was I highlighted Um, I just highlighted there's just too many too many states in the world. So it's it really doesn't make sense Uh-oh, and can you imagine that because there are way way too much data and tree map visual has crashed my system excel One thing I discovered with excel 2016 is not as stable as 2013 So it kind of crashes your system more often than it should so Let me jump back to the slides And let's finish off where I said we had q and a English language queries And then we have show values as now show values as for those that use pivot tables who uses pivot tables here How many of us are pivot table kings? If you are not, please you need to be how many of us use pivot tables or is it pivot tables? What's new in excel? Yeah, okay. Okay. Excel is trying to tell us what's new in excel, which is good I'm just gonna close that close this Okay and Yeah, so show value as analytics. So this is pivot tables you have Show value as was a pivot table tool that power bi has borrowed So in pivot tables when you have your values, you can right click a value in pivot table and say show value as Percentage of grand total percentage of that total. We now have that in power bi very cool Then super custom visuals. I'm going to show you the narrative visual Um, but these are super custom visuals are also very very key I call some visualizations in power bi, but I'll show you how to get that narrative visual And then the last thing that we think was the best thing for last year were connections There are far lots of new powerful connections sap new connections to sap in very new connections to many things I think they're up to a hundred now. They're like a hundred connections This means you can connect power bi and excel to a hundred different data sources through power query So you can imagine connecting to oracle sql Tags excel even online on the web and stuff you can connect to nearly everything Which just so powerful. So for example, if I come here I can just go to get data and I can everything you do in power bi You can do it in excel The only thing you can't do is this new visualizations all this new custom visualizations You can't really get those in excel only use what you have in excel Or if you're extremely good in excel, you can build it yourself to in excel. So Here you could come let's let's quickly get that custom visual when you're in power bi there are two ways or even Two two ways you can get custom visuals There's under the home tab you have the custom visual section here where you can get from file Which means you've downloaded the custom visual already and put it on your desktop or somewhere and then you connect to it Or you get from that's from store From file. So and then you get from store, which is you go online. So let's go to the store Let's say from store. So I'm clicking on from store now to click on from store. You must make sure you've logged in You have to have logged in already So I'm clicking from store. So what power bi is doing is going into the store Microsoft store and then is listing out all the visualizations you have. So that's from store So here you have all you have editors pick you have kpi So if you know the visual you're looking for something to do with kpi you click on kpi You see these are the kpi visuals Custom visuals that you can bring into your reporting. Yeah So these are custom visuals not standard visuals, which I have to the right Custom visuals. So what I'm looking for is narrative. So let's see The search can work for us and I'm just taking nara. I hope it does it have they improved it. Let's check. No, they haven't So you have to actually find the full name of the spelling. So I think usually at the bottom. Let me just go down narrative visual narrative narrative narrative, where are you? If you have to know exactly how to spell it Narrative it's like a pen. They use the pen to depict it Oh, the narrative visual is hiding from view. Let's say advance analytics You see if it's there advance analytics because to me that is like an advanced analytics So utilize Dot journey. Yep. Here we go Narrative for business intelligence. That's the name of the visual narrative for business intelligence Discover insights hidden in your data and automatically transform the data into dynamic narratives So all you do is add You click on add. So what it does is going to now add that visualization into your Like say, okay, and here we go. This is the narrative visual So when you click on the narrative visual, you would have to now know how to use it But again, once you go online, you'll be able to see how to use this visualizations online because they will download a sample visual So let me show you how that's done If you go, let's just quickly go online And then we download it because that's what I think you should go practice that to go and check that out I'm going to go online Okay, let me just take this out a bit All right, so I'm I'm going online. So this my this is actually my office 365 connection I want us to go to All you need to do is that power just type power bi custom visuals So I just google type power bi custom visuals that should appear. So you have the app store find the right app So there we go So probably just type power bi custom visuals there and you should be able to get the narrative visual So once you do that you'll see custom visuals for office 365 excel excel also has a lot of apps So we need to click on power bi visuals And then we do a search For the narrative visual or is our search box Okay, the search box usually is not as smart as we would like But let's just eyeball it and let's find the narrative visual Uh, I don't know this won't help. Let's Where is custom visual? Let's look for the narrative and see if it comes out Yeah, we go. Cool. So it even got an office app award in 2017. How cool So if you click don't click get it now just click on the click the visual itself You would usually see a sample So what happens is I click on the visual You usually see if I go to reviews You have the sample there. These are people's reviews someone reviewed quite low But I've only been able to get the app to work once. Okay. Well, something's up with the system. So Here we have this if I click on this It gives us a visual now typically you should have samples So maybe this guy's didn't put a sample which is sad But other visuals have a sample file which you could download And and and then you'll be able to let's say get it now. Let's go to get it now. Maybe that's where they put it Get it now continue And taking you to power bi visuals to complete this process Okay, so they kind of made it a bit longer But they've moved all the visualizations into microsoft store before they were directly on power bi dot com So we have to now go to the store microsoft store. Well, don't worry. It's free. This is a free visual So you can select to download the visualization or download the visual or download a sample report To the right, which is what I was looking for download a sample report And here we have a sample report. I'm going to put it on my desktop Uh, just put it on the desktop All right, so save So here we go is saving the sample report And then I can now open the sample report I want to open the sample report. You can now use it and practice it before you now practice it on your own real work So I think that's pretty cool. So it's opening up on my system now And then you practice. So I advise you go get this visual I think is the best visual for last year and even got an award for last year last year and they keep updating these visuals every time So, um, always go and look for updates as well Now if you use uh windows 10 Most of the updates will be done for you. So this is the sample report. This is what it looks like Once you download it, you read how to use it And then you go check out how they used it in various reports So look at this report. For example, you have your typical data and then let's say you have an executive scorecard This is your executive scorecard, which is designed like for your mobile. Yeah An industry margin analysis. Let's see I'm looking for narrative. Where are your narratives narrative? We should have a bad one with a with a good narrative Revenue by state No, no narrative here So this is the only small narrative I think I saw But again, you can read it cool not to write a story because the narrative extension could not interpret the data for Was an issue with the narrative for power bi api This is not a very good advert for narrative. This must have been a narrative here So anyway, check it out and we can check out what was wrong with that But that's how you get your custom visualizations and put it in All right And then of course we have our trainings online if you like go and do the free Excel one or even send the free excel one to someone, you know, that would need some excel skills So these are the courses we have on our online course e-learning Course website, which is office training hub dot com If you want to pay for our courses in nara, you could go to our website dbrown consulting dot net Then go to training and then you go to online courses So once you go to online courses, you'll be able to pay for those courses in nara. So it's a bit easier So and and they're quite quite cheap So I recommend if you haven't done this report automation in modern excel This would teach you everything you need to know to automate any reporting excel very powerful And so we're going to be talking in the webinar something similar to scenario sensitivity analysis So we're going to see you in the webinar for For power bi i am enough for power bi for financial modeling So but before we go, please could you answer another quiz question? Because I'm wondering if How long everybody here has been using excel? So I'm sorry to just ask Okay, so we have some serious gurus here 30 percent of us have used excel for more than 10 years Okay, the percentage is going down 25 percent Have used excel for more than 10 years. Wow, excellent But I hope you learned something. So if you did, please just type in your Question box type or what interesting thing you learned today that was new And then we're going to call it a day because we're going to prepare for the next webinar So just type out what you think you learned today that was new in the chat And then please once once it goes off they have a small evaluation to fill if you can feel that that'll be very nice So switch is the main thing and I know people are going to be using switch a lot switch switch Okay, text join some people like text join are they going to use text join And again three maps someone who wants will want to use the three map three map By the way, you cannot use three maps with a pivot table So that's just a sad thing. You cannot use three maps with pivot tables You need to have your data in excel itself for you to able to use three maps all right Yeah, you can use it in pivot tables So what you do is you can use some funny formulas that extract data from your pivot table and put it on an excel And then you use your trim up. Yeah All right Okay, so good. So we continue these webinars every last Thursday of the third Thursday of the month So it's continuing again next week. You will get an email from us Requesting for some ideas that you want or you can even send an email straight to training at dbrown consulting dot net Join our newsletters as well and We hope to see you next a month and of course if you're doing any if you're doing the financial modeling We also see you in the next one hour. So thank you everybody and we'll have to say bye. Bye now I'll leave you with the power bi Website page which just shows all the wonderful things you can do with power bi. So see you next month. Bye. Bye guys