 Hey guys welcome to SSUnitech, so see this site and this is continuation of Power BI projects. So today we are going to discuss one more project that is INC 5000 companies analysis. So basically here we are going to use the INC standard companies and will do the comparison between those. So let's see this beautiful visualizations which we have used inside this dashboard. So here in the left top you can see the company logo which is our channel like SSUnitech and after that we are having the title. So this title is going to indicate like INC companies analysis and below of this in the left top you can see the first visualization that is area chart. So in the area chart we are going to consider like revenue final which is our revenue by industry type. So our industry type could be health industry and it may be different industries like food industry, real estate industry. So we are having these industry types. So we are doing the comparison between the industry types as per their revenue. So in the left side you can see the health. So the highest revenue that we are getting by health it is around 38 billion dollars and after that we are having other industry types as well. So we can scroll little bit on the right side and we can check each and every industry types and the comparison between those. After that in the right top we can see this tree map chart. So basically this tree map chart is going to indicate the revenue by the name of the company. So here we are going to consider only top 10 companies as per their revenue. So here we can see this prime. So this is the first one in the top and the total we can see the revenue of this company is 21.4 billion dollars and after that we can see this Uber technology is the second one which is having 11.3 billion dollars and after that we are having other companies as well. So this is the top 10 companies as per their revenue and after that go to on the below of this on the left side we are having the cities and the total companies. So this is very straightforward like we can see in the New York City we are having 165 companies. Those are registered inside the INC standard. So after that we can see the Chicago. So Chicago is also having 110 companies. So according to that we can check the cities and how many companies are registered in the INC standard on that city. So we can check that data on this table. Now go to on the right side this table. So this table is very important table. So here as we can see the first column which is going to indicate the company name the second which is the industry type after that when this company was started. So we can see the founded after that the city in which city this company exists after that how many workers on that organization. So we can also check that and next is the revenue. So this is a revenue of that company and after the percentage growth. So these are the columns available. So we can do the comparison and we can check and we can also do the sorting on this revenue or in the workers we can do according to our requirement. So let me do the sorting on the revenue so we can directly click on that and here we can see little bit like 1 billion and after that we can see other venues and the founded date we can see the worker we can see let me do the sorting on the worker. So in the worker we can see this is the highest worker and after that this is the second one then this is the third one so we can check that value. But one thing that you should notice over here we are having underline on this name because we have set a hyperlink on this name so we can click on that and it will be going to jump directly on the website of that company. So let me try to click on this so once we click it will open and we need to wait few seconds. So this is the company website so we can directly click on that and it will open the company website a few of them is not having the underline. This is because the URL which is specified in the source is not correct. That is why we are unable to jump on the website of that company. So this is what we need to do in this video. So first of all go to on the data source and let us have a quick look of the data source. So our data source again in Excel so here the first we can see the rank after that the profile then we can see the name then URL and after that the state after that revenue then percentage growth after that the industry type then how many workers are working on that organization and after that the previous worker. So we can also do the comparison with the previous worker and what worker we have in this year. So the next one when this company was started then year on list after that is it the metro one then the city. So this is all the information that we have inside the source now go to on the power we are training and we will try to design that beautiful visualization. So here first of all let me get the data from the Excel file. So here we can see the import data from Excel so we can directly click on that and if you are not using the latest version then you should go on the top side and you can see the data and here we can see the Excel so we can also get the data from the Excel now let me select that file and click on open so here we can see the second one so we can select that seat and here we can see everything which is required to us but here one thing that you can notice inside the revenue we are having the values in the millions and after that we are having values in the billions as well but we don't want to see the values as per million and billion we want to see the values directly with the numbers so we can go to the transform data and click over there so it will open the power query so inside the power query we can edit our source and do the transformations on that so here as we can see in the steps so we have added these steps by default so that is okay but here go to on the column that is revenue column so go to on the right side and here this is the revenue column so first thing that we want to do we want to create a duplicate of this revenue so how we can create the duplicate column so for that go to on add columns and here we can see the duplicate column so we can click on that so it will add a new column that will be the copy of the existing one let me try to rename this column name and call this as revenue new so this will be like revenue new and after that we want to split between between these two like the numbers and after the character which is a million or billion so how we can do that so for that go to on the transformation and here we can see the option to split column so we can click on that and here by delimiter we want to do the split so we can click on that and here we can see it is asking like by default it has selected the space so that is okay between the numbers and the characters we are having the space so by using that space we want to split it into two columns so let me click on okay so it will split into two columns so like this as you can see like revenue new one and two in the two we are having the characters and in the one we are having the numbers so that looks good the next thing that we need to take care like first of all we have to create one more column if we are having the millions then we should have the six zeros along with one and if we are having the billions then we should have nine zeros along with one so how we can do that so let me create a new column so for that go to on the add column and here go to on the custom column and we need to wait few seconds so here we need to write our statement like if the revenue and this would be all able inside the columns so we can directly jump on that and double click on that so it will be going to add that column so we need to check the value if this value is equals to million then we want to see one six times zero otherwise in the else we want to have one with nine zeros like this now that looks good and after that we can click on okay so it is having this custom column we can also rename that name like we can call this as anything like revenue value something like that we can click on okay so this is revenue value now our revenue final will be the multiplication of this revenue value and revenue new one so how we can do that so for that we have to create one more column so again that will be the custom column so we can click on the custom column and let me call this as revenue final so this is the revenue final and here we just need to multiply with the revenue new one and revenue value so how we can do that here go to on the columns we should have revenue new one so we can double click on that then use the asterisk sign for the multiplication and revenue value like this so that looks good and we can click on okay so this is our revenue final but here one thing that you can see we are having the data type that is string and numbers but we want to see this as numbers so we can go to on the decimal numbers and we can do the conversion on this so that looks good now everything is fine inside the query editor now we can go to on the home close and apply so it will be going to load inside the power way desktop so we need to wait few seconds so now here we can see data is successfully loaded on this now go to on the existing one so first of all here you we can see we are having an image so in this image we are having the logo and ILC companies analysis so this image should be used in the training one so let's have a quick look of this image so here let me open that image so this is the image as you can see so let me close this and go to on the training one and let me have the that image over here so for that go to on the insert and here we can see this image so we can click on that and it will ask so we can select that and click on open so it will be going to load that image over here so let me try to make it bigger like this now it looks good then very next thing that we need to take care like in the existing one we can check we are having the first visualization that is area chart and inside the area chart we are having the industry type with the revenue so now go to on the training one and let me try to get the area chart so we can click on the area chart and try to put in the left side of this image now try to make it a little bit bigger like this after that in the axis we want to see the industry type so we can drag and drop the industry in the axis and after that in the values we should have the revenue final which we have calculated so here we are having the revenue final so we can drag and drop that in the values so that looks good and after that we can make it some so that is fine and we can see these are the values that we are having the next thing that we need to select this and go to on the title of this and try to make the title as we want so inside the title like revenue final by industry so that is fine we can leave it in the font color we want to make it as white and in the background color we can have the other color like this so that looks good and after that we can go on the x-axis and y-axis so we don't want to see the title of the x-axis and y-axis so here title we can off that and similarly we can do for the y-axis as well so we can off that title so now this looks good the only one thing that is remaining the background color of this so we can also make the background color of this with light orange I guess so here we should have the option for background color let me try to make it this one and transparency we can increase so now it looks good so we can see the values like the health which is having the highest value and the others are having the less value than health care now this looks good next thing that we need to take care we are having a tree map chart so in the tree map chart let me have that in the report and let me put that in the right side of this visualization like this like that so here first we should have the company names so in the company name we can directly drag and drop the company names in the group so that is the name so we can drag and drop in the groups that is fine and in the values let me try to drag and drop the revenue so this is not the correct one it should be what we have calculated the revenue final so now it looks good and after that go to on the filter of this and here let me add the filter of the top 10 companies only so we can drag and drop like this here we can use the top n and here we want to see top 10 only on the basis of revenue final now we can apply this filter so it should be filtered only top 10 companies should be available over here now it looks good and after that we need to do the modification little bit and do the formatting of this map chart so go to on the format and here we can see the data color so we can change the data color as per our requirement I am going to leave it and after that we can see the title so in the title we can also have the background color of this little bit like light orange like this and font that should be white or this one so now it looks good and go to on the data levels so in the data level I wanna on this data levels so everything that is going to our label over here as per their total revenue so that looks good the next thing is the table so go to on the existing one so here we can see the country wise total count so here let me have the table so we can select that one and put it below of that and it should be like this and like that now first we should have the city so we can set the city and second how many companies are available on that city so go to on the names and click on that instead of seeing the names let me count that so we can see like one one and we can see everything like that if we do the sorting then we can see the New York Chicago and going forward we can see the other values now only thing that we need to take care for the formatting of this table so go to on the format now go to on the style and here we can have a little bit like this or like that so now this looks good and we can let it increase this and increase that and we can make it like this now it looks good the only thing that you can see the count of the names so we don't want to see the count of the names we can see the total companies so we can rename this and let me have the total only we can rename again like this so my looks good now the last table that we have over here put on the existing one and here we can see we are having the name then industry type then founded city worker revenue and growth so let me go here let me add one more table and let me try to put on the right side of that table and make it bigger like this and like that the first column that is a company name so we can select the name the second column that is the industry type so we can select the industry type next one when this company was founded so we can open that founded and select that and here it should not be some so we can do the don't aggregate so that looks good the next one the city so we can select the city after how many workers we have so we can select the worker and here again if we don't want to do the sum so we can use the don't sunrise then the revenue of this so the revenue which we have calculated and it should be the sum so we can see the sum that looks good and the last one that is the percentage growth so we can select the percentage growth and here again we don't want to do the sum so like this so here we are having each and everything the one thing that we need to take care for the formatting of this so let me do the formatting after that we'll see how we can add the hyperlink on this so go to on the format and here go to on the column headers and the background color that we want to have like this so that looks good and after that we can go below the font family that should be aerial and we can also increase this little bit so now it looks good now go to on the name try to add the hyperlink on the names so we can directly click on the name and it will directly open the website of that organization so for that go to on the fields and in the names we can click on this little bit around this here we can see the conditional formatting so in the conditional formatting we are having an option to web URL so we can click on the web URL and in this source we are having a column that is the URL so field that is fine only values after that here we can see the name so instead of the name we should select the URL so it will be going to open this URL so we can click on okay now it should have the underlines on this so we can see like we are having the underlines so little bit do the formatting one more time go to on the values and here we can change this as aerial and we can little bit decrease this that looks good and after that now let me try to click on this and it will open the website so we can click on that and we need to wait few seconds so here we can see this is the company website so that looks good but here you can notice in some places we don't have the underlines this is because the URL organization is not correct so let me try to verify go to on the data and let me select that table here you can see the URL if URL is going to start with the sttp and sttps so it is going to treat as the URL but in some places we can see we don't have the sttp and sttps that's why we don't see the underlines below that we can also reduce this problem you will try on your own first you should check if we are having the sttp or sttps then you should not going to include anything before that URL if you did not find the sttps or sttp then you should include the sttp or sttps so you can do this on your own and if you stuck then let me know I will try to help you out on that scenario thank you so much for watching this video if you need the source file I will provide the link of Google Drive on the description of this video so you can go and download that and start the practicing on that so thank you so much see you in the next video with interesting dashboards thank you so much