Data analytics using AI with Excel and Power BI 2020 - no programming required

So let’s get started. Many of you already know me, so I’m not going to do the intro part, but quick one. This is what I do. They’re going to cover primarily two topics today. Excel and power BI. Now for those who may not know Power BI earlier, the logo of power be I used to be like this and now it has changed. This is the new logo. So in case you’re looking at documentation, you’ll start noticing this logo. This was launched just few days back during Ignite.

00:28 - So what are you going to get at the end of this session? You’re going to get a lot of clarity about how to use both the tools, how to. Save alot of time. And you’re going to get a lot of knowledge in the process. I’m going to cover 15 things, five things about data cleanup, five things about data shaping, data shaping as a concept is comparatively new for people who only use Excel. But in power BI it applies. And then of course 5 things how to use AI for data analysis. Before we do all that, I’m sure all of your Excel enthusiasts an most of your day goes in looking at the Excel screen, so let’s do something simple for ourselves.

01:11 - Let’s reduce the ice cream by using the Dark grey theme. So remember all the office tools have a dark grey theme, and if you’re using teams, teams also has a dark grey theme. Windows also has a dark grey theme, so let’s see how to do that. It’s very simple, but it’s easiest to show it as a demo, so I’m going to do this like this. I go to file, go to options and you will see office theme by default it is white. Change it to dark grey. I’m right now changing it in Excel.

01:46 - Doesn’t matter where you change it, it’s going to affect all the tools, so it’s simple an effective. Now let’s start. There are a lot of concepts where to cover, but. Before we go into concepts, let’s start with the demo first. In the demo I’m going to show you few things. Then we will go to concerts. Then most of the session is a demo. This demo I’m going to show you is a simple one. Probably some of you already know it, but because data cleanup is one of the topics I’m covering this. Normally when we have data like this and you want to split it, usually we go to data and text to columns, but now next to takes 2 columns now means. Since 2013, actually there is a feature here called Flash Film. How does that work? It’s something like clean up by example. What does that mean? Instead of you doing the clean up you splitting, you specifying what is that element? Are you specifying what you want as the output and then putting formulas yourself? You just inform Excel what you want by giving one or two examples. And then you saw the shortcut there. That is control E. And it does the job.

03:06 - How does it work? This sort of is an algorithm, not exactly AI. It’s pattern matching or regular expressions. But send me, you can say now if I Type 45 and again press control he will remember the shortcut. From here it works. It is not always guaranteed to work. ‘cause what is it doing? Is finding the pattern. So in this case if you look at this particular example, the pattern is not clean because the data itself has different delimiters. In some cases the delimiter here is. In this case the delimiter is. Bracket here it is colon. Here it is 5 and so it gets confused but doesn’t mean anything sometimes what happens it actually goes in? Does something different in the sense it will not work as expected.

04:00 - So I give an example and then type control E. It did not do the job correctly in some cases. Where did it not do the job it’s trying to buy a find a pattern, so right now Excel is thinking, oh, this person wants uppercase characters. That’s right, gave him for management and marketing instead of MGM T. So it made a mistake. So don’t get disheartened. Don’t give up. Go to the mistake. Change it corrected, improve it. Now notice while I’m typing it, it selects the whole range to visually tell you that it is going to affect everything again, so it thinks again, and generally it works. So this is simple easy flash fill.

04:42 - Now it was just a quick demo to get started, but when it comes to data, how do we manage data more effectively? The cycle is simple, whichever tool you use, the cycle is by and large the same. What is the cycle? This is the cycle, so you get the data, clean it up, shape, visualize. Share the exact steps will depend on the complexity, but by and large this is the process. Unfortunately I’m sure all of you know that the biggest time consuming task here is the cleanup part and it overshadows all other aspects. I am aware that the attendees here are from different backgrounds, different industries, different levels of seniority.

05:23 - We may have some CEO’s as well as some students, so some of the topics I cover may or may not be directly applicable to you, but. Just keep an open mind, understand and think if this is not useful to me. Is there anyone else around me in my company, someone reporting to me or my boss, whoever it is and then share this knowledge with them? OK, so now when it comes to clean up, that is very inefficient so when it comes to inefficiency, there are two types of inefficiency in the context of data analytics. One is the operational part, operational means we’re spending too much time in data cleanup because we don’t know the. Faster, smarter, more efficient method. That’s number one and 2nd is analytical inefficiency.

06:09 - What does that mean? We are analyzing data, but we are not analyzing it fully. So as we go along, we’ll understand. Now when it comes to data cleanup, we’re talking about two products, so it may be confusing, so let me clarify that first, there is 1 area which is overlapping between Excel and Power BI. Both Excel and Power BI have a common tool which is called power query. And that is an amazing tool for data cleanup.

06:39 - Now before we go ahead, you need to understand how different concepts. Map to Excel versus Barbie. I so this may sound or look like a complex slide, but I will try to explain and as we go along you will understand it more and more effectively and easily, even if you don’t understand this at the end of it. The important thing I’m trying to show you here is that in the context of Excel and powerbi there are some skills which are common. For example the same process which I showed you earlier. I’m showing you here. The first step is important cleanup. Fortunately, that part, which is inefficient, time consuming, laborious repetitive is common in Excel and power BI, so the benefit of that is whether you learn it in Excel or in powerbi.

07:28 - It’s exactly the same, so are getting dual benefit. Where is it now? Remember I’m talking about the latest version of Excel, so exactly what I’m showing may or may not be available to you and your version of Excel, but as and when time comes, I’ll explain how to install those addons. So in the context of Excel, you should have data tab. Of course we have that if you have older version of Excel you will see get external data in the new version of Excel. It’s called get and transform in Power BI.

08:00 - Also it is called get data and transform data. So here get and transform. Here it is get and transform. Where does the data go? So we import the data, you clean it up that happens in get and transform or get data. Technically that tool which cleans up data is called power query. Now the next step is to create relationships and put the data somewhere. Now in Excel we have two choices. We have Excel sheet itself, which is always a problem.

08:28 - Many of you have posted queries saying we are running out of the limit of 1 million rows. That is a very common problem, but even if you’re not running out of the 1 million row limit per sheet, what is the other problem? Forget 1,000,000. If you reach anywhere near that or even half of that. Everything becomes slow, so scaling large amount of data has always been a problem in Excel. So Excel has a database and that database is called a data model.

08:58 - Where is that visible? There is a tab or menu called powerpivot. If you don’t see this menu, go to file data and say enable powerpivot. I’ll show you that option so that everyone is clear about it. Sometimes it happens that you have that option, but it has not been enabled. So how do you do that? So go to file, then go to options and in the data tab you will see a checkbox called Enable Data Analysis. I didn’t score Powerpivot, Powerview, and 3D Maps. Powerview has been deprecated, but Powerpivot is what we’re interested in while we’re at it. Might as well enable the 3D Maps as well, so once you do that, what happens? You will see this particular menu called Powerpivot, so this data model is ideally the place to store data. Excel Sheet is not the ideal place to store data WHI will understand soon in case of power BI there is no Excel sheet so it directly goes into the data model. So this is the menu of power BI on top on the left side we have three tabs, so the middle tab is the data tab.

10:08 - The first tab is where we create visualizations, and the third tab is where we create relationships, which is here shaping of data we will see later. Now for analyzing and visualizing the data, what do we have in Excel? You know we have pivot tables, pivot charts and conditional formatting and various other things. Now power BI does not have that part of Excel, so it has this report tab. Which is basically a blank surface and you put different charts, visuals, Maps, all the sharing of reports. Traditionally. How do we do in Excel V? Send the file to each other or nowadays we put the file on OneDrive or teams or whatever cloud storage and send the link to each other.

10:47 - Whereas in power BI you can send the files, but it’s not recommended because with large data files are going to be unmanageable, but a better option is to put it on Power BI portal and share the link from there. Now sharing the link from Power BI portal. Costs, whereas this part of Power Bar, which I’m showing you which is power BI desktop, is completely free for life with no compromise on features. So you may be told by some people that poor buyer to pay that is partially true initially to start working, get the reports done, get the business benefit. You do not have to pay. How do you get power? Be I desktop? Go to powerbi.microsoft.com. Log in using a non consumer ID.

11:31 - Or do I mean by that not Hotmail, Gmail kind of IDs here corporate ID even if your company is not purchased anything from Microsoft you have a company email ID or even a college or school email ID will work. Then go there, log in and download power be I desktop and then what you get is this and finally the last topic we will cover later. Basically where to get data imported in such a way that the same piece of data. Is shared amongst people. Normally we share reports, but the raw data everyone downloads on their desktop, which is a confusing thing which is an error prone tick. How do you centralized that? That is also a part of power by now if you have data in power BI, people can share the raw data and create their own reports. Now in Excel you can use that as well.

12:21 - So this is a good time to show you how power BI integrates with Excel. So in Excel if I go and go to data notice I’ve get. Data in get data. I have various options, I can get it from file from database from Azure and many many other things. But one of the options here is power BI. So if I’ve got some data have put it on power BI and I want my raw data to be used across the organization, then I can do it from here. So my data where is it coming from? I as a person wanting to analyze the data, don’t need to bother. So someone who knows about the data.

13:00 - Which could be a senior user experience person in the company or a database person, or someone from Mighty has created a central source and everyone in the organization can use that as the authentic, reliable source of data. So if I click here, Notice what happens now. Excel is talking to my power BI system and this will of course require the paid version. Paid version means $9 per person per month. So now what is it doing is talking to my power BI and checking? Are there any pieces of raw data which have been published so that everyone in the organization can use them? So in order to make sure people can rely on that data and trust that data for accuracy? I take and certified so certified are flagged like this, so if I go to go Scroll down you will see non certified ones also.

13:53 - So as a user you can publish but certified are more reliable, more trustworthy and someone who understands how to manage data is maintaining them. So that’s the importance of certified. Everyone cannot market as certified. Few people are given rights to mark datasets as certified. Now what happens here is when I click on it. We expect the data to come in Excel. No data doesn’t come in Excel at all. It directly creates a pivot table. There are tables associated with it. I can straightaway go and start creating a pivot table out of this. Now, where is this data setting? Of course it is sitting somewhere.

14:33 - In the Silem now I am able to analyze this data completely the way I would typically do in a pivot table. The benefit is when the server data server data changes. What happens when the server data changes? It is going to manage the show automatically and when you say right click and refresh it is automatically going to talk to the server and get the data refresh. So the part of getting this data every month, cleaning it up, massage ING it, and then doing this jugglery again and again is completely eliminated. We will see this in greater detail, will see the whole life cycle.

15:13 - But at this stage this is enough for you to understand, alright, so let’s go further. I recently did a LinkedIn survey and this was the result. I ask people where does your data come from and you’ll be surprised to know what people said. Even today, 45% of data is still coming originating raw data in spreadsheet. I’m not saying people are doing data entry, they may have exported some report to Excel, but still it is in spreadsheet and the moment you export it, it’s a snapshot. It is not going to change.

15:45 - When the underlying data changes, so that’s a problem. Other 36% is CSV now, even if the data source is different, CSV is different from spreadsheet. Agreed, but where does CS we go? People are going to 1st double click on it, and CSV opens in Excel. So actually spreadsheet is 44 + 3680% of people are still using raw data in a spreadsheet, which is fairly intensive in terms of manual work involved, and it is very much error prone. Now when we go ahead, we will see many, many different features, but there is 1 feature in Excel.

16:22 - You must absolutely know and use for every piece of data, and that feature is called tables. I will send you a link to a detailed video and articles about how to use tables in detail, but broadly, what do you do? Is this if you have data anywhere in once the data is clean, it should become a table. For example, this is my data. And this is a table. How do I create a table? I will show it to you quickly here itself, so I’m going to copy paste the data here. This is not a table. I’ll remove all the formatting. This is regular data assuming it is clean, I will explain what clean means. You select the data, go to insert and choose insert table. It checks whether there are headers.

17:06 - Sometimes there are no headers, in which case this checkbox will be off. So you just say OK in this case now it becomes a table when it is a table you better. Table. Design menu here. In that it’s a good idea to give it a better name. Excel is going to give a name, not a problem, but it’s better to give a name which is sensible and you can identify this is very important because this table name you are using in multiple places, you can use it in formulas. You can use it while importing data in another file or in powerbi, so make sure as soon as you create your table you give it a better name. OK, now.

What are the benefits of creating a table? 17:58 - There are lots of benefits. There are 13 articles are written about it. I will send you the link and I will also send you a link to a couple of videos which explain each of these benefit in detail, but in very very simple terms. What does it do? Whenever there is a formula and you’re referring to the formula, it automatically updates the formula that is the single most important feature of table. The other important feature of table is in a column it automatically creates.

18:32 - It automatically creates formulas and copies them so you don’t waste time in dragging double clicking gaps, coming, formula, stopping all those problems are gone. Some of the experience people use named ranges, but you know that name range is a range for which you select first and then give it a name after the name is frozen. If you add more data below, it’s not going to expand. So if you want that to happen again, table comes into picture. So lots of benefits of table. See them later. Now we will switch tax an come to the other aspect of tables.

19:10 - Before we created table we have to make sure that the data is clean. The problem is we don’t have a good definition of what exactly clean data means. So if I give unclean data to many people, so I call clean data good data. And data which is not clean I call evil because with this troubling us every day and wasting our lives. So how do you know if I give you bad data? Ask you to clean.

Of course you will clean, 19:40 - but the question is will everyones clean data match? Most probably not, because nobody has taught us in a manner in a language we can understand what exactly clean data means. If you talk to database people, they will give you all sorts of jargon. So here is a simplistic definition of what this means. So this is a link this slide. This is going to require you to pause and think, so I’m going to pause for awhile. I’m going to be on mute. Take your time and read what is written here. The ones in black apply to a column of data. And the ones in blue applied to the whole data. So now I’m going to be on pause and read it. Think about raw data. I’m sure you have some questions about this. No problem. I’m going to show you samples of some of the ones which are difficult to understand.

20:37 - Some of them are of this must have a heading. No duplicate headings, no brainer. Some of them are not easy to understand because I’ve just written one phrase there, so I will show you examples. Of few of them. So here are a few examples. One of the things which we saw, which is not easy to understand, is only one meaning. What do I mean by that? And no formatting. What do I mean by that? So let’s see the no formatting part because that’s easy to understand.

21:11 - No formatting means what no formatting, instead of, meaning that is what I mean. What is the purpose of that yellow color? What is the purpose of that red color? If I ask you that question, what will you tell me? He will tell me I can’t answer that question because who will know that only the person who wrote that or put that yellow color? Agreed, let’s say I’m the person who put that yellow and red. How long am I going to remember what yellow means after 8 months I’ve used the same yellow color in 40 different places for completely different reasons, so it’s not easy even for the person who puts it. The bigger problem here is there is a business meaning for that color of formatting. Bold yellow, whatever. That business meaning needs to be analyzed. So for example, sooner or later you’re going to require total of yellow and total of red, which you’re not going to get because formatting cannot be analyzed.

22:11 - And because we want to analyze data, cannot be in the form of formatting. That is what I mean. So how do we manage this? It’s easy. Remember when you need data. Remember this is a very important thing. If it is data which needs analysis, then what do you need? You need a column so whatever is the meaning of that yellow red, put it in a column like this. So maybe yellow means this red means that fair enough. Now it is easy. It can be analyzed, and if you still want some color coding you can do it using conditional formatting. So best of both worlds. That’s what I mean.

The other one, 22:53 - which is not easy to understand is 1 type in one column. So how do we do that? One type in one column means what? Here these are three columns, but what is it looking like now? This data is numeric. You can see that this data is Jan, Feb, March and this data is FY. But this is text, this is nothing. Excel things Blank is different than a finite in is different human beings think this means FY19. This means Feb. This means Jan for Excel it is blank. So this is 2 meanings in one column. The answer is obviously are to fill the gaps.

23:37 - How to fill that we will see later, but you understand what is bad data. The other one I want to explain how to grow horizontally is bad, so this is a master or attendance record. This column is good, it grows vertically, but this is bad because every new date we are going to add a column. So how is the data growing horizontally? That’s bad. Why is it bad? Because once it grows horizontally then this is going to stop at the end of the month. Then you will add a new sheet.

24:08 - College Jan, Feb, March at the end of the year you will put another file. It’s a lengthy process and you’re increasing complexity of analysis for yourself. Ideally, this data should be in three columns. And play ID, date and status then life is good. So anything which grows horizontally is bad. And finally, I talked about Subtotals. Very often we get data with subtotals like this. These are typically reports which we export. We have no control over, it reports are created by someone and we get this in Excel. That’s OK, but report is a final output. We are using it as an input now. So then the bad parts have to be clean. Ideally try to get it from source, if not somehow you have to clean it. So this is what I mean by horizontal totals. It should not be there. Raw data is raw data.

25:01 - All the subtotalling we will do in pivot table or whatever other tool don’t try to do it in the raw data. Raw data should not be worth looking at. Raw data will have repetition. Raw data is not good to look at, is good to analyze so that we can create something which is good to look at, which is the output. That’s the concept, alright. So in Afghan now let’s go further. And see some action. Now let’s come to a demo again. What are we going to see in the demo? I’ll show you 5 examples of data clean up very, very quickly. So one of the examples of data cleanup is called unpivot. All of us know what is a pivot table. But what is unpivot? So here is an example. This data is in bad format.

25:51 - Just apply the principles which we saw just now. You know why it is in bad format, right? Why is it in byte format? Think for think for a few seconds and tell me tell me means, think and tell yourself which rules which we saw just now are violated here. So these are the rules. And this is the data. So obviously multiple rules are violated. This is growing horizontally. This is not a collimating. Data is sitting in the place where Collimating should be, and so on. Never mind. But now if you think of it, does it not look like output of a pivot table? Yes, so the fact that if this was a pivot table then no problem? Yeah, this is not a pivot table. This data is entered like this. Then it’s a problem. So we want to somehow convert it to three columns and make it clean. So here is a demo. You go to data, get external data.

26:51 - There is an option here where you can pick up data and range. In this case it’s a demo, so I’m showing it ideally close this file, go to new Excel file and then connect to that Excel file by going to get data from File Excel workbook. It will show the table we have already given a table name. And then like this, so in fact I’m not going to show you how to do it from here. I’m going to close this file. I’ll create a new file. Close this original file. So that we have the correct habits from day one.

27:27 - So I’m going to save this file and close it. Now I’m in blank new file. There is nothing in this file. Now I go to data. Now I go to get data from file from workbook and I will pick up that particular file. So let me go to that file, which was a crosstab file. Now notice this is get data or power query, the very powerful tool which I talked about for importing and cleaning. What happens here? It is showing what is there in that particular file.

28:00 - We know there was one sheet, but we never import sheets in power query because it will always have some extra empty cells, headers, titles, some random stuff. So notice there are two tables, so when you click here it does show you look at what is going to happen, have some data here. Some data here is going to import everything confusing. That’s why table is important. So now we have a clean piece of data. Now at this stage here to think, is this data clean or I need to clean it? So what I mean by that? If I need to clean it, what do I do? That is the question. So at the bottom you will see there is load or transform. So if the data is clean I say load.

28:46 - If it is not clean, I want to clean it a sober word for cleanup is transformed. So click on transform and now probably some of you will see power query for the first time. So this is all about query looks. What is it doing nothing into just showing you the data as it is. So what do we do with this? What do I do with this? I’m just going to change it to Monospace font so it’s easier. There’s no way to increase the font size right now.

29:16 - I’ve tried to increase scaling but just live with it. Now the problem is this is like a pivot table. We want to undo that pivot table. Don’t even think of manually doing copy paste special transpose. Now what do you do? This column is good. The product giant to December columns are bad. So what do you do? You select the go to column. It could be more than one column also and then right click there. A simple way to learn everything is right click. Remember now what happens to look at all the options. There is an option called Unpivot columns unpivot which columns we don’t want to unpivot the good column? We want to unpivot the bad columns, the ones which are not selected. So you say I’m.

But other columns and now what 30:02 - happens at nicely converts it into tabular. Properly denormalized, clean data, it doesn’t know what to call these two columns, because we just created them, so I’ll just call it month and the second one will call it quantity. We’re done. Now we say go to Home tab and load the data. Now when you’re loading the data, this is small data, so it’s OK to put it in Excel sheet. But we have a choice if you know this is going to be very large data and for example this was a CSV which you know is many many million rows, then obviously it’s not going to fit in an Excel table which is going to go in a sheet.

30:44 - So in this case large data you see only create connection and put it to data model. What is data model? We saw that it’s a database sitting inside Excel which has nothing to do with Excel sheet. So proper database. In this case I’m going to put it in Excel sheet. You can put it in both places, but generally it’s going to waste double space, so don’t do it. So now what did we get? The original data is as it is not touched and we got clean data. This is how you use power query. Even if the data is coming from Excel.

31:18 - Don’t clean the data in that Excel file itself, because it’s going to occupy space twice. Close the file, open a new file, import it in the clean way. Now if I open the original file and add some record, what is going to happen? That is the obvious next question, so let’s do that. I’m going to put one more recording here. And for the time being, I’ll just put 10 here. An I’ll save the file, close the file.

This while obviously is not understood that 31:51 - part, so you just right click and refresh and it will do the job. So this is the way regular day-to-day work. Periodic manual cleanup is completely eliminated. First time little efforts second time onwards refresh. That is the benefit of power query. Let’s go ahead and look at another demo in the same situation. We also have a very common requirement where we have a folder and in that folder we have files. So let’s say I have a folder. This is the folder. An in this folder files CSV files 3:00 to 4:30 or 3000 doesn’t matter, the process is same. How do we consolidate it today? Manual, copy, paste, copy, paste or some people write macros? No need any longer. What do we do? We go to Excel in this case I’m going to use this new sheet which I created already where we imported. I could have done another sheet as well, so let’s add a new sheet here and see how it works. Again, get data.

Where is the data now this time 33:02 - it is not in Excel, it is from file. It is a CSV file but it is not one file. So what should I do now? Which option should I choose now so I go to data again, get data? And file, but this time folder is a very powerful feature. So when I say folder, what does it do? It goes and looks at ask me where is the folder path which I will just copy paste. Now it analyzes the folder, finds out what files there are. It is showing you the files. We don’t know what the data is, so it’s a good idea to load.

33:42 - Of course, load yes, but we want to combine first. If you say load just this data containing the file names will get loaded. The contents of the file will not get loaded. We want to combine. And it’s always better to check whether anything needs to be cleaned up. If I was very sure that the data is clean, then I don’t have to say transform. I can directly load. In this case I don’t know, so I’m going to say combine and transform. Now it checks the files. Now it needs to know which file it should consider as a sample file because Excel is looking at the files right now for the first time, it needs to know which file it should take as the sample file. The requirement requirement is all files should have the same format, same column, same data types. So which file doesn’t really matter? So first file click OK. Now what happens? The same power query an in that power query.

34:36 - What do we get? Something different this time? This has done a lot of jugglery all these steps were created automatically. All these steps were created automatically. What you essentially got is the data combined from Feb, Jan and March files. If it had just combined the data on four columns, he would have lost track of which row belongs to which file, so it has added one more column called Source Name and it has kept it here. If you just want to change the name, I can call it file. I don’t want that thing to be.

35:15 - I just want the word fat giant March, then what do I do right click like we have Excel split column. We have split column here also an unlike Excel. Of course you can choose delimiter but in Excel every space would get here I can see only leftmost and then it will split it and then I don’t need this column. So I remove it an I rename this tool file and I’m done. Notice whatever steps I’m taking our IP automatically getting recorded.

35:45 - So this is conceptually like recording a macro, although the language which is being shown here is different, you do not have to learn the language on day one. So I’m done now, so I say close and load this time also I will load it in Excel sheet. You don’t have to worry about how this happened, it’s automatic. That’s that, it’s so simple. The steps remain exactly same, even if they’re within 1000 files in that data right now. Now what happens tomorrow? In that file, someone sends a file late, so I’m simulating that by putting a file. Here I paste it here.

36:20 - Needless to say, he just say right click refresh and it will do the job. So amazing way of cleaning up one time, combining, consolidating and continuing that consolidation. As long as it takes. Another very simple but very useful thing is for data containing multiple data types, which is the fill gaps part of it. So fill gaps. Example. What is the example? We have data which contains gaps. Like this I showed you one sample, the same similar sample. I’m going to do.

36:57 - Same data, I just created a table out of it. Now remember we want to fill these gaps. We want to fill these gaps. The data column gaps. We don’t want to fill because they have a business, meaning these are not business meanings. In fact, this is confusing. In this case I will show you how to do it in Excel itself to save time. So if the data is in Excel, anyone to import in the same file, which is not recommended but just for demo purpose I’m saying then it says OK. Pick up the data from the table. You’re already in the table. So power query opens. You know the story. Now notice power query shows a small little thing here.

37:34 - What is this showing? This is a small chart which shows you something extra. It shows you in this column what is the quality of data. It’s saying 7 are valid. There are no errors and 16 empty cells and that chart is showing you the as like a mini bar chart there. Now what do we do? We select both these columns and somehow we want to fill these gaps. Empty cells are shown as null here. These nulls we don’t want to touch so select only these two cells columns rather right click look at all the options, not replace values. Remember, because replaced with.

38:12 - What if I eleven 12:20 so fill down one click job is done. And then you say close and load and you’re OK to go. So now it’ll add a sheet and give you the data done. Of course, when I add more data here, it is going to refresh. So that is very powerful. Very easy, very quick. Some of you may know go to special blank equal to up Arrow control and copy paste special value that’s no longer required because even if you know that. It’s not going to.

38:45 - Update when new data happens, you’ll have to repeat that process now onwards just right click refresh. Ali, another one which I’m not going to show because the process is exactly the same, but I’m going to talk about is PDF import. Many analysts get data in PDF format and PDF copy paste from X to Excel is a disaster. Everything goes haywire even if there is a table in PDF. The table is not retained in Excel. So in XLS well as in Barbie I there is a PDF option.

39:20 - It understands pages it understands tables inside the pages. If a table is breaking across pages and has the proper header, it can even combine tables across pages. It’s an amazing tool if you uses PDF as an import, just write out at Bureau works beautifully. Once you select the table again, it’s going to say transform and the same power query where you can clean up data. Then finally there is web import. I will try to show you one simple example and then we will go ahead.

39:51 - What do we have in the web import? Let’s say you have some data on the website so. I’m just going to go to a website. Which has the table. Lots of websites have table stock market websites that are table. Covered, it is all your reports, whatever it is, so I’m just going for a simple table. Tallest buildings in India now. This table if you try to copy paste this is going to be a disaster. Look at this, it’s a problem to list under construction like that. Proposed. Like that, so it’s a problem.

40:31 - How do we do this? Don’t even think of copy pasting, just copy the URL, then go to Excel in Excel. Where do you go? Again? The same old place data get external data and then what do you do? Where do we go now? So here itself there is web available. It’s also available in other sources web. And then what happens? Alaska, you are just specify the URL and you’re done. Now what happens in the URL? It analyzes the page. It’s an HTML page, it understands what is HTML page.

41:09 - It tries to find tables in, it shows you all the tables. We don’t know which one, so you have to do a little bit of trial and error to find out which table we are actually interested in. They say hold no proposed. Some tables have not been given the name by overwrote it in Wikipedia. So you find the table you want. And yeah, this is the one we want it right now. Is this looking claim? Does look clean, but always assume there will be some cleanup to be done.

41:38 - It’s always a good idea, even if you are confident. It’s better always to, say, transform, so I’m going to go and say transform data. Parkway reopens, you remove the unwanted stuff and then you get the data I’m going to do this quickly because you know the story right now. In fact, it is caching the data. Actually, this particular web page there more 215 days back, so it has remembered that so never mind. So now you look and see which columns do I need. I need the name column.

42:11 - So what am I doing? I’m selecting the columns control click. I want this. I want this and that’s it. Because very often we need subset of columns. Now you select and they remove other columns. Very often we get dumps which are many columns and you need only few so that is not affecting your speed of operation in Excel. In Excel would have to import the whole data which makes everything slow and cleaning it up is even more slow.

42:40 - That part is completely gone now life is better. So now I’ve got this data. Of course I can split this into something. If I want to, let’s say I want to keep only meters. So what do I do? I’ll go here, right click, split, split by delimiter. In this case split by which day, Linda? Space which it understood, but I only want the leftmost. I don’t want all those columns to appear, so I say OK, it will split it. It’ll create two columns. The second column I don’t need, and notice it automatically change the data type. This is the data type it has detected. This is a text, this is. Decimal it automatically understands if it doesn’t understand then you can also change the data type here without formulas. Now I’m going to remove this column and then we are good to go and then say give me the data is going to talk to the webpage. Add a new sheet, give you the data. After one year, some new buildings have come up. No problem. Right click here and refresh. It will do the job again.

43:45 - If this was coming from a dynamic site like let’s say. Stock market side or something like that? No problem you can manually refresh it as well or becausw this itself is a table. You go to table properties. In table properties we have query properties where you can say refresh on open in case you forget to refresh and while the file is open also you can refresh periodically. Now you can make a pivot table on this and it will refresh also. Pivot Table will have to be refreshed manually, but data can refresh automatically at the desired frequency, so those are data cleanup examples.

44:26 - Now if you have questions, of course you can put them in the Q&A panel. I hope you know where is the Q&A panel for those who are new to attending on teams. Live meetings, I’ll show you there’s a small little icon either on the website or here. There is a kernel button. Ask a question. Try to put your name. Don’t put an animus questions an if reqd put the slide number. Alright, let’s go further. Now we will talk about shaping of data. Now this part I’m going to show you in Power BI.

45:03 - So when you import data, what happens? The data comes in Power BI or Excel. In Excel it goes into data model or in sheet. Now data model is this. This is power BI. This is model. This is the data itself and this is the visuals where we create the visualizations. I’ve imported 2 tables. I remember what happens here. One table could have come from CSV. One could have come from a database, doesn’t matter. But now the problem is I have a city ID in this table and I was separate city master where also I have a city code. In Excel we would have done.

45:50 - We look up by adding a column in the transaction table here and say we look up city ID from there and get the data that’s no longer required. So when it comes to. Data shaping this is the most important concept. Which eliminates the we look up we use for decoding purposes. So what does this do? It’s very easy. Like what we have been doing in all kinds of databases. How do we do it? Simple, you go to this tab called model. If it finds it automatically creates the relationship like this. But I will show you how you should do it manually, even if it detects it automatically. It’s a good idea to check whether it has detected it properly, so I imported 2 tables immediately after import. It will show the two tables, but they are not connected to each other.

46:47 - So you should know what is the primary key here and what is the foreign key order related. We look up column basically. Now, unlike we look up the order of columns doesn’t matter here, as long as you know both the columns. So I know the city code here is the one which Maps with City ID. Their names can be different. Data obviously has to be related, so you drag and drop. Whether it drags from here or there doesn’t really matter, it understands it.

The only requirement is because it’s a 47:19 - city master. The city code should be unique, otherwise it’s not going to work. It will give you an error. What is it showing? One and star. What does that mean on this side? There is only one city and on this side there can be multiple transactions based on that city. That’s what it means. One to many relationship for those who understand technology, it’s called cardinality. Even if you don’t remember that word, that’s fine. So this is the first part of shipping.

47:49 - Now when I go and analyze the data, what happens? I get a blank page here. Assume this is blank and here. I will see all the columns. So what happens now? I’m going to see all the columns like we see in Pivot table. This is a simple example. In real life it can be a much longer list of data. So in order to make this list as short as possible. Without compromising anything, we do the next step in shaping.

48:19 - What is the next step in shaping this side, whether every column needs to be shown or unwanted columns can be hidden. What do I mean by that? For example, City ID are we ever going to do any analysis on this number? No? So, I don’t want to show it. I’m not deleting it. Remember I don’t want to show it when I go to create the report based on this, this city ID is not doing anything. So what do I do? I go to the data tab, right click on City ID and say hide in report view. Exactly the same features are available in Excel Data model as well.

49:02 - If you import this data in Excel sheet, none of this is available. That’s why I’m saying going further in order to get benefits of this entire system, you should try to import data in data model. So Hayden report view in Excel the same manner is called hiding client tools. Now my now it becomes sort of grayed out so like that unwanted columns you hide all right. So that’s show and hide in the city master. Notice what I’ve done.

I’ve already hidden the city code 49:36 - and some other random ID which was there which is not useful for our purpose. So you hide the columns. The third thing you do is decide how a field is summarized. This is a very common problem. In Pivot table you must have notice that you drag drop some column it becomes. Around then you wanted some more. It becomes somebody wanted count and the default behavior cannot be changed for every pivot table you have to change it. How do we solve that? That is also solved very easily by using summarization kind of shaping. What does that mean? Look at this. This is the year. This is just for demo purpose. I have a date here.

50:21 - I really don’t need this month and year column but just for demo purpose I’ve given it here. Now when I drag drop here, here is a numeric column, so by default it is going to do some. I don’t want that to happen. So what do I do? I go to column tools, click on the column. Column tools will come automatically. Then go to some. I don’t want it to some. In fact I don’t want it to do any kind of calculation, just keep quiet. So no summarization. Simple. Now age is another very common problematic thing. Some of age has no meaning, so whatever done here, I’ve changed the summarization to average because that’s what typically we want.

51:05 - So if there are some product codes which are numeric, again, you say no summarization. So that is about summary. There’s another very common problem when you put data in data model and then it doesn’t work and you get frustrated. What is that? Look at this. I have a column called Month so if I go to my data here and. I just drag drop the column called month. Let me do it on a new page. I have a column called Month or do you expect it will show me all the months? Great but it is not sorted correctly. If the data is in Excel Sheet and Mega make a pivot table, even if it’s not a date, even if it is text it understands.

51:47 - This guy doesn’t seem to understand, so this is very frustrating for people who are new to power BI. This is by design in Excel that happens because of custom lists, but in poor by there is no custom list because custom lists are difficult to maintain. So the solution is another kind of data shaping which is called sorting order. How do we manage the sorting order? You create a column, so look at what I’ve done. This is the month column. I also create a column. Which is a man. What is? This is a calculated column. What is this doing? It is just using the month function. So it’s giving me one 234 up to 12. Now.

I want to tell Power BI that whenever I’ve 52:35 - used this column called Month, how do I want to sort it? So I go to sort by column and here I say MN. Now of course, this column, A man itself, is just for this purpose, so I don’t need to show it in my data. So in this particular column, what am I going to do hide it from report view? Now when I go back to my report because I’ve changed the sorting order. What will happen when I drag drop month here? This will be automatically coming in the correct order. It’s not only for months. Many times you have locations, product names which have to come in a particular order based on the norms in the company, and that’s how this can be done.

53:23 - Custom sort and the final type of data which data shaping is data category, which is also very important. What do I mean by data category? Here is an example. Let’s say I have a number, but actually that number is a PIN code. How do I mention that so for the time being just assumed this was pin code? How do I mention that that’s called data category? Data type is number, but this is a PIN code, it’s not. But just for demo purpose I assume so open this dropdown under column tools and then you say.

54:03 - Whatever it is, because it’s numeric, it can only be either a bar code or Postal code. But wait, we have cities, right? So you say city, the name of the column need not be city. The name of the column could be XY, zed also. Or I’ll say location. Doesn’t matter, but we know it contains city. Barbie I or data Modeler Excel doesn’t know this content cities. So what do you do? Go to and categorize. Remember earlier I went to a numeric column. Now it’s a text column, so categories change. We have full address, which can be a street. Address kind of thing by the way. It does understand St addresses so many people spend time, energy, money to geocode addresses. Try this out.

It understands places, 54:56 - cities, places, means it could be village. Also it could be name of a famous place, that kind of thing. City is a proper city, country, state or province pincodes. It understands very well countries obviously. And in case you have some data coming from your logistic system or something else, you can also understand Lat Long.

55:19 - Lat longs are understood not in minutes and seconds, but in decimals, and there is an easy way to convert that those who are in GIS know that if you don’t know there are third party tools or just search for it, you will get the formula of how to convert minutes and seconds to decimals. So anything which contains Lat Long will typically be a decimal. So it will be considered as a decimal column as far as the data type is concerned, but you come here and then say which one is latitude, which one is lanja tude. So that is the 5th type called. Data categorization, So what are we doing before we start analyzing the data? What is the benefit of doing this before we start analyzing the data, we are enriching the data by giving it relevant context at a centralized level so that we don’t need to keep customizing the output when we actually analyze the data. Alright. Now. I’m going to take a pause for a few seconds to hydrate, and then we’ll continue.

In the meantime, 56:21 - what should you do? Just you also take a pause and think. Alright, I’m back, so let’s go further. Now we will come to the AI part of it. Where does AI come in comes in different places and in different ways. So we’ll start from simple to complex. There’s nothing complex really, as you will understand when we go along. Now. Typically you will think that AI is used for analyzing data, finding correlations, statistical significance trends. Yes, that is true, but there is one part where AI comes into picture for something as simple as data entry, that is something you probably don’t know, so I’m going to show you one small slide because it’s a no brainer, but it’s just that people don’t know about it, so if you have Excel, mobile, mobile version of Excel.

57:18 - What does it allow you to do on the Excel mobile version or? In office app, so office comes as different apps plus combined app in both of them there is an option called image to text. If you have Excel app. Audio office app. Both of them have this option. What does it do? So when you click on this option, it will start the camera. It will allow you to take a photo of anything which has data on it. You can see it’s crumble. It’s not perfectly held. All that doesn’t matter. This is a very small table I’ve picked up, but I’ve tried it with large tables. If you hold the camera steady and the resolution is good, it works beautifully.

Now what does it do next? 58:05 - Once the capture is done, they’ll ask you to identify slika crop. Just position that correctly. It need not be a perfect rectangle can be skewed slightly tilted, it’s OK. And then it tries to use AI to do. Analysis of the numbers, text and all that and then shows you something like this. Now part shows you or zoomed version of the image and lower part shows you what it has detected. It is still not copied to Excel, it is in edit mode so to say.

58:36 - So if you click here it’ll show you highlight where that is coming from. If it is a spelling mistake or some number mistake you can correct it there and then. And finally when you’re done either you can copy it and paste it anywhere or open it in Excel. In which case the data will go directly in Excel. Of course, this file will be on one drive, so now you can open the file on desktop.

59:00 - It will sync and let’s save you a lot of trouble of data entry, so it’s even data entry can be simplified. Try it out. Now there is another feature which is important in Excel which. We will see next, which is called ideas. But before we see the ideas feature, you need to understand why you need this feature. So here is an example similar data. I have some. Few columns. 11 columns and 1500 rows. Raw data will make reports pivot table charts. How many reports you make depends on how many boss is asking for, or if you are the boss, you will decide how many you need based on your perception of your business, your expertise, your knowledge, experience, whatever.

So typically what happens is if there is a 59:54 - common numeric column, everything by. Amount by city amount by countries. So whatever 789 reports will come. That’s the way we generally think. But the important thing here we have to think of is a concept which I mentioned earlier. So let me go back to it. We talked about something called analytical inefficiency. What do I mean by that? I mean, if I say I create 5 reports out of this. Of course those my reports are useful. You’re going to use them. You know how to act on them all good. The problem is why did you stop at 5 or 7 or 10? So we need to step back where so used to mckeating data cleaning? Adopt making reports, dumping it on each other, and see if doing reviews we have not stepped back.

00:47 - So what is inefficient here? I’m not talking about the operational part. Operational part means the personal is creating the report is doing it inefficiently. That part we have already covered and that is operational. This is analytical. I’m not talking about wastage of time in data cleanup. So what does analytics mean? That is the step back we have to do.

01:09 - So analytics in simple terms means what are we trying to do? We’re trying to get the data from past, whereas this data come from. Whatever data you have. Whereas it come from obviously typically the data is historical. OK, so we have data. We have trying to learn from it useful things, not random things. Data is always historical. Yes, you may have forecasts, but forecasts cannot be created unless you have historical data. And then do what tried to take some action so that the chances of future being in our favor are higher. More than random chance. That’s the whole concept.

01:52 - The question is how many reports? 510 whatever. Why stop at fuel? We must try to learn all possible useful things. So few. Here’s what we do. Entire world does few reports. Here’s some people may take a pivot table and also do some proactive exploration, but they will get bored at some point of time. They will have some time constraint and they will stop at few plus more, nobody can confidently say I have tried to find all possible useful things and that is the crux of getting rid of analytical inefficiency. So with this thought in mind, let’s see how various features can help us.

02:33 - And this is where the AI comes into picture, WHI whi do you need I hear becausw in this case. Let’s say I have decided that I am going to spend extra time and learn all useful things. It’s a good thought. It’s possible also. For example, create a pivot table and try to put all combinations of each column in row column and drill down and all that. Yes, you can technically do that, but every variation of pivot table you create is not going to be useful. Some of them may show you useless stuff, some of them make sure things we already know or is not relevant. It may show correlation.

It may not show causality, 03:13 - so you don’t want to waste time. That is where he I can do this on your behalf. Find out what is interesting and then show it to you on a platter. So that is the concept of ideas. How does it work? This is the data. Make sure it’s clean. You know what is clean data now and in the Home tab you will see our ideas button. Now remember this requires some very sophisticated AI algorithm. Microsoft took 1 ¹⁄₂ years to perfect this algorithm. It just got released in. Public preview a month or two months back it was in beta for 1 ¹⁄₂ years, so when you click on it, what is going to happen? This data will be encrypted, sent to Microsoft AI engine. It will analyze it, show you what. From an AI algorithm perspective is interesting, useful or statistically significant and then. It will delete the data, it doesn’t keep it on your server. Microsoft, there’s no human being looking at the data, so when I click on ideas button you need an Internet connection.

04:28 - Suppose you have Office 365, then this will work if you have a desktop version of Office. This will not work. Maybe you have Office 365 subscription but you have older version of Office on the desktop then this works on browser version of Office 365 three as well. So what has it done? It has gone and analyze the data in a neutral, unbiased manner, and that’s important because when we analyze data because we know our business, we tend to analyze based on what we feel is important. I am not saying what you feel is important is not important, but what you not notice also may be equally important. If you don’t know it, it’s a loss of opportunity.

05:10 - If you find it, it’s a competitive advantage and this feature will give you that competitive advantage by just one click. Of course there is a limitation technical limitation for this is how big can the data be. So it’s not about number of rows and columns, it’s about number of cells which are submitting for analysis. Right now the limit is 1.5 million cells, so whether they are in horizontal or vertical format doesn’t matter. 1.5 million cells. So if you give more data is going to use a subset of it.

05:42 - This data is not anywhere near that, so it has analyzed it fully. So what is it showing me? It is showing me things which it found statistically interesting or significant. For example, it has done a scatter chart of age and amount in a typical business scenario, nobody would have asked for this report, but it has done it and now that it has done it. If you find this interesting, what do you do? You can say insert chart so without you having to decide what goes in X axis Y axis, it actually adds the chart. Now when you look at it, you’ll realize that. People about 50 less transactions.

06:21 - Maybe because there are less people about 50 or it depends on your target audience, your business, your customer segmentation, but maybe something useful may come out of it. If you investigate further. That’s the idea. OK, so I’m under delete this, but there is not just one report. Scroll down if you Scroll down what happens? There are more reports you must see all the reports now. This is an interesting one. They’re showing you that the trend is going downwards so it. Found the regression line that is showing you the actuals plus the regression line. The overall trend is like this. Now there is a plus sign here.

07:00 - Notice earlier it had inserted a standard chart. Now it is saying what do you want to insert? Pivot chart. Oh, but I have not created a pivot table no problem. So you say insert pivot chart lidar. She ate. It led the pivot table. It will give you a trend line. Now what you will see is what is the for those who understand statistiques? What will you go? And do you will first put the R square if Bhaskar value is above.5 Gen related significant events nearer to zero.

07:30 - It’s not significant or not to worry about in this case, this sounds like a useful piece of information over the years we are targeting or we are getting younger people. Now that’s a piece of information you may not have thought of in the regular course of time, but this is coming to you on platter. That is the concept of. Ideas now if you go back it will show you more and more ideas like this and you look at them. The other important thing to understand is what kind of analytics does it do? It does four types of things. What are the four types? Four types of things it does.

08:14 - Maximums, correlations and stuff like that. Four types of things it does in the analytical part. So we have seen some examples of that. An equivalent of that is available in the. Barbie as well. It is called quick insights. I will show you how to go there, but concept is same but it analyzes more patterns in Excel. It looks at trend. It looks at maximums. It looks at majorities and it looks at correlations whereas in power by the same concept feature is called Quick Insights. It’s more sophisticated. It looks at 9 different things.

08:55 - So that is the benefit data still can come from Excel or CSV, but do it in power BI quick insights. I will show you how to do that in Power BI quick insights, so I assume you have imported the data. Assume you have done some dashboard but to generate quick insights you don’t even need to create a visual, just import the data, save the file and then publish it to power BI portal. Publish it, it’ll ask you to save published the fight once you publish the file to Barbare Portal, what happens? It will publish the dashboard. For viewing and sharing, that is, number one and it will do something else.

09:34 - So remember what I’m saying if you just want quick insights which are more sophisticated in power via import the data in Power BI. Don’t make any visual, just publish it and then what happens? It will try to do the required stuff and finally give you a link for that thing. That thing is quick insights so you will get a much longer list of things from the data so notice. It also. Did that. It has found a trend line and so on and so forth, so this is a more comprehensive analysis. Even if the data is same. All right, let’s come back. So that is what is available in Excel. Now let’s go to other types of AI. Now I’m going to talk about things which are available only in powerbi and not available in Excel.

10:23 - So let’s go to the same dashboard which we have. So here I’m publishing it. I choose a workspace, publish and you’ll get the quick insights link that I’m not going to show now. So now I have created a dashboard here. How do you create a dashboard in Power BI? You’ve got the data and you add a page and in the page you put visuals. Like we do in Excel, you choose the. Chart or visual and then for example, I chose Pie chart which is here and then you see what goes what is shown and drag and drop. So similar to a pivot chart basically.

11:04 - But there are some very nice features which are AI oriented. This session is not about how to use power BI and day-to-day basis, but this is about how to use AI features in Power BI to improve the quality and the extent of your analysis. Let’s talk about Kenny. Because when you present something to anybody, typically we pop copy, paste in PowerPoint or in US Excel itself or in Word, and then in the review meeting people are getting static data. Even if you have the bitul very rarely, there is a standard operating procedures that when. You are in a meeting, yes, by all means you can have static images, pictures, data, pivot tables, copy pasted charts.

11:53 - But he should also have an interactive dashboard that has still not become the corporate standard in spite of by being a well established concept for decades. And that is something you should look at even if you don’t have a ready, beautiful looking dashboard. Whatever data you originally used to copy, paste those reports in PowerPoint. Carry that data imported and carry it in a Barbie I engine. That is what I mean. Why is that? Because it allows you interactivity. And not only interactivity, it allows you to have open ended questions asked. Now, whatever is the level of your preparation you have created all reports which you think everyone is going to ask. There will still be some questions which are new and the common extensor for those questions is what I will get back to you. I’m sure you’ve heard it before and we continue to hear it in today’s world. Also, in spite of all the boom of data scientists an by.

12:54 - In general we want to avoid that because if you hear the sentence I will get back to you. What happened, everyones time was wasted. We cannot reach a conclusion or decision and action point. We have to wait for that to come. Minimize that. That is analytical efficiency improvement. How do we do that? So example, one method of doing that is ask question. Now I’m going to try my luck. Sometimes this works, sometimes it doesn’t. I’m saying the feature works, but I’m trying a more sophisticated version of this feature.

13:28 - Where is that feature? This is called Q&A. Or you can just go anywhere and double click it, create secure in a box. So what am I doing in the kernel here? I’ll just try to increase. The question feels size so that they can see it clearly. OK, so. Sorry statement. So you can type a question here, and where does the question come from? Questions will include names of columns we have, so you know what columns we have class state.

14:11 - Card expense type gender amount cash back here very good Now I type a question but actually because we have dictation feature also in. Windows, I’m going to try my luck. So now notice I’ve clicked here, but I’m not typing. I’m going to try a dictation. Obviously this requires a good microphone and Internet connection, so let’s try our luck. So I. I click on this then it will start listening to me and then ask a question. Amount by location. OK, first time it goes wrong. Don’t worry, try again. Amount by location. Not is not only did it understand it actually created a chart. Now I don’t want a chart like this.

15:22 - I can always say I don’t want this like this. I can say as map for example. So it understands it’ll draw map as well some city. It has not understood properly, but never mind one city has gone here. That is OK, but it did understand what I asked for. You can ask very complex questions here an it works quite well, so very sophisticated, kind of AI. And it has a very very wide vocabulary. So let’s say top. Let me type one more. No problem done now. I don’t want to see this like this. I want to see it as pie chart done.

16:12 - And now if you want this as a part of your dashboard, just click on this button. It goes down. It also has some amazing other features. For example, I want say I call this revenue as top line. Now it says I don’t understand what is top like, no mine. I’m typing what I want. Now it’s saying OK, I understood what is card type blue means. It understands top line, it doesn’t understand. So you can teach it. Remember, it’s AI machine learning so much and is learning you teach the machine. How do you teach the machine. You said define top line, then it will give you a complex looking dialogue. Don’t worry. I will explain, so it’s saying what does top line mean in your for formula or in your data? So if you go here. It doesn’t understand what is stop lying now. What do I do? Oh, there are many ways of doing that.

17:13 - One method is our data shaping that I did not mention in data shaping. You go to your data. You know the column name is called amount. But actually in our business we call it offline. So you go to this and there are synonyms here and then I can type us in on him. Now, it doesn’t matter if there is a spelling mistake now it will understand. So now if I type top line, it should understand it. So now I just wrote the word top line. And if I’ve defined that correctly, maybe I made a mistake there, then it will understand it. For example, I have given other column names here whatever I said. Revenue there is no column called revenue with us. But if I say revenue. It will understand. That has total of amount notice, so this learns and you can define terms and many many sophisticated things can be done in Canning. OK, that’s great. So you ask a question. It gives you the result.

18:24 - Alright, So what do we do? Next, there’s another feature called Related Feature which was introduced just 10 days back, but it’s related, so I’m going to show you. What we did here is we asked a question and it gave her the answer as an output as a visual. So we type text Ann. It gave visual OK. The reverse is also possible. For example I have some visual already. Here is a simple line chart, but I want to understand it and I want the understanding to be commonly established because there is a problem with lots of visuals. Many people like visuals, they put complex charts, but people may be interpreting. In their mind, completely differently, and nobody cross checks what is shown as interpreted.

19:11 - So everyone thinks they understood the data, but they have interpreted wrong things that will never come to the fore. So how do we make sure? So there is a summarized option, so this is a reverse. It will look at the visual and summarize what it is trying to tell you. So notice what it did. It put a sort of our text box, but it is an AI based text box. And what is it trying to show us? It is very small font right now, but I will try to show it to you and a larger size hold on. So it is giving me something here.

19:59 - What is it? Trying to do it is trying to interpret the data on my behalf and trying to show me the interpretation right here. So let me try to increase this font size and show you. I have increased the scaling factors so it’s difficult for me to. Look at it here, but just give me a second. So what is it saying? Let me right align it. An setvalue review. OK, let me try somewhere else. Which has more space so you can even go to a dashboard which has multiple items. And even if it has multiple items, it can do this so this visual is called smart narrative. Normally what we do is we copy paste a chart in PowerPoint and then we try to go there and annotate by putting arrows and markers and then the chart changes and our annotation is still the same. So that part is now taken care of. And because this interpretation is right in front of everyone, at least everyone is on the same page and misinterpretation. Is unlikely or less likely, So what is it saying silver accounted for 68%, and so on and so forth.

21:30 - You can also add your own stuff here if you go here, you can put your own values where you can put a question the same Q&A or even you can create a calculated value. So I’m just going to put a calculation here. So by the way, this. Q Any supports what Kearney supports? Journal sports. Arithmetic also. So for the time being I’m just putting some random arithmetic amount of mine and divided by age, which may or may not mean anything in practice, but just for them up office. So notice what happens. I created something and. It is finding that there are two items there. I type it, it gives me a value.

22:21 - I can put the value here an this will also get added. So I’m going to make this value a little bigger so that we can see it. Now remember this is a dashboard. What does that mean? If I filter on something of everything is going to get filtered. So if I click on silver card, that description is automatically generated. Life it’s a brilliant way of managing the show. Smart narratives out of the box. Part of Barbie.

It’s a new feature, 22:52 - so if this make sure this version is new then go to file options and in the options make sure because it’s a preview feature, it was introduced few days back. Or do you have to do go and enable that preview feature so in the data or three options you go here? And then there are lots of options. One of them is called preview features at in that make sure you have enabled smart narratives only then you will see that button. The other feature, which is equally useful is this. I have this simplistic piece of data notice in generated this also, but never mind now.

Obviously everyone is going to ask 23:34 - me why this happened, so don’t say I will get back to you. Right click on that, drop from June to July and say analyze. Explain the degrees. Now what is it doing? Remember we wanted to do something very special to avoid intellectual or analytical inefficiency. What did we want to do? We wanted to learn all possible useful things and that is alive implementation here. It analyzed all the parameters available in your data. All the columns are dimensions.

24:05 - It ranked them in the influence they have in that decrement from June to July, and it is showing you the highest ranking thing. So in this case it seems gender is influencing that decline when revenue from June to July, and it showing you a nice waterfall chart. But that’s not all the second ranking factor seems to be type of expense. Entertainment went down, travel went down. And others were small contributors. Then we have third one and 4th one and 5th one like that. This also has another important thing you should know. For example you have 2018, nineteen, whatever. Here I have expense types. I can also see it as a scatter chart. Or I can see it as a standard stack bar chart, or even better, better than stack bar chart or ribbon chart. What does a revenge or do it teachers stack bar chart, but it does ranking. Now notice the data being shown. Here is the same. Of course, there’s 100% stacked bar chart, so we can see proportions, but we can’t see ranking easily.

25:14 - This guy shows you ranking very easily so pink, which is travel, which was very high second rank in June has gone to 4th rank. And something blue Entertainment, which was first rank in June, has gone to lost rank. This is important and we are missing out this kind of ranking. In every stat chart we see, so this is additional benefit. Star start using ribbon chart in addition to stack chart. And then you will understand better. And the last AI feature I want to show you is. Decomposition 3, which is like an interactive exploration of our data. So this is how you go beyond your get field and manage the show. How does that work? Let me show you this is the. Thing it looks like a horizontal or org chart.

26:12 - So when you click on the visual it says what do you want to analyze. So typically it’s a key parameter, important parameter OK, and then you want to analyze it by what? So I put month expense type I can put many items here. I’m also going to put state. As many as you want. I am also going to code class of city and location which is City itself. OK, nothing is changing the order in which you are had things. It doesn’t matter when you click the plus sign.

26:46 - If you want to do a exploration the way you feel your contributary factors are. Of course you can say OK, break this down by card, in which case it’ll break it down for you. And then you want to break this down further by something else, OK? So this is experiential means based on your experience, you are deciding what to click on next and break it down by what that is one way of doing it. This itself can give a lot of insights, but there is a better way of doing it like we saw earlier. I want to know amongst all these factors what influences amount most.

27:26 - And I don’t want to do that ranking manually by trial and error, so you say. Break it down further by the highest influencer amongst the ones I have chosen. So this is where I is used, and then it’ll break it down by whatever it happens to be. In this case card. That bulb icon means it was the I generated. OK now I want to see breakdown of gold card again. I don’t want to decide which parameter I leave it too high. Love that so it will find the next one. It happens to be gender and then the next value and so on and so forth. So this should be a part of every review meeting for every piece of data. If nobody’s interested, you do it. You will find additional inputs you can benefit from them, and you can also grow in your job so you’re not fulfilling demands for reports. You are generating additional useful insights. Now notice what is the order right now. Card gender, expense type. And class.

28:26 - This is for gold gender expense type class, but maybe I go to. This one. See the order. It may or may not change depending on what is the correlations. So if I go to platinum, something may change. So this order will change depending on the kinds of correlations I finds. So notice the last one change to state now. So this is how interactive exploration of data is called decomposition tree. So that’s the kind of coverage I wanted to do today. Fuel. Winding up things and then we will take questions. Of course, I’m sure you have questions. Keep adding them. I will try to handle as many as we can till 5:00 o’clock and then later on offline. So what is the call to action? I’m hoping that you found this useful. Of course in one another, for us there is a limit to what depth we can go to, but I’m sure it listed as stimulated some thought and ideas which you can go explore and implement.

29:34 - So what is the call to action? First, think about what you’re doing. Whatever you want to try and go about and doing it, don’t take a large complex existing use case and try. Once you’re learning and applying, start small. At least something you can start doing tomorrow onwards you start using the Clean Data Checklist to make sure your data is really clean as per our norms or the recipe for clean data. Try to start small and improve. Existing process is but don’t just improve for yourself.

30:06 - Think who else can benefit from it and make it as a standard operating procedure so it becomes person independent. I’m sure you have realized that a lot of things which happen in the context of Excel especially are so person dependent. If those two people or five people leave people, your balance sheet will not get consolidated or your financial statements will not be ready for the quarter. Is that person dependent? Another thing is published the data centrally and I showed you how to consume central, authentic raw data from RBI into Excel. What is in? I use artificial intelligence with your natural intelligence. That is what annoys.

And then of course you’ll be 30:51 - able to analyze data more fully, get more opportunities, and then achieve more with less effort, right so? I guess it’s perfect timing. If no more questions. Let’s call it a day. Thank you for joining. I hope it was useful to you. I enjoyed delivering the session and hope to see you soon for many more sessions and see you in the group very soon. Thank you. Bye bye. Take care. .