How to create Power bi report from SharePoint Online list (Donut chart example)

hello and welcome to the channel my name is BJ and I am a Microsoft MVP in this video I am going to explain you how to create power be a report from SharePoint Online list also to be these are the points which we are going to discuss how to create your be a report and then we will see how we can select the required columns from the power very poor not necessarily we require all the columns whatever presented in our SharePoint list and then we will see how we can format cell phone list lookup column how can change the data type we can rename column names and then also we will see how we can add visualizations into the power bi report and specifically we will see how we can add a donut chart in this power ba into our power bi report if you have not gone through the previous videos that I have created in the power bi section that is what is power bi what is power bi desktop how we can install power bi desktop and then also I explained how we can what is the difference between power me a free proven premium but essential just to go through those those videos I will put the links in the comment section of this video and before starting this makes sure that you have added this or you have installed the power bi desktop so that we can use that to create the reports so I will open my power bi desktop so I will just source for power bi desktop and then click on open and it will open my power BX tech stuff from where we will load our SharePoint list data so before till it is loading I will open the SharePoint site you can see here there is a product list which is having a column which is having a column as title vendor this is a lookup column so there's even the list I will show you so you can see here this is the vendor list we charge the vendors are there and this is the title column and then apart from that there is customer name quantity price sales delivery charges city and location so these are the columns basically presented in the serpent list and so I will go back to my sir my power bi desktop so just close it and there here this is what is the power bi desktop looks like when you open and here first of all we need to get the data from the serpent list so these you get data option is there if you are using or if your data is in Excel then you can directly click on this but since our data in the online service so I will click on this gate era and then click on more and this will open a dialog box where we can see all the data sources that we can select for our power bi report you can see it here I will click on online services and if you'll see here this option called as serpent online list apart from that there are various other data sources which are supported and if you have the requirement you can think those but for this example I will select serpent online list I will click on continue so when I will click on continue it is asking me to provide the SharePoint site URL where the where our serpent leads to exist I will go here I will copy the SharePoint site URL you don't need to copy the lista model but you can copy the site evil and then click on ok once you click on ok since I have connected it before so it is not it didn't ask me about the credentials but maybe for your case it might ask you for the credentials and then I will select the columns sorry the tables or the list so in this case I have product list and I have a vendor list so these two lists I will select make sure you select the list which you want from this it will populate all the list from the SharePoint site that you have entered now if you will see here there are two options that is asking one is load another is transform data and if I will actually if you will see this this lot of other columns are the hidden columns that we have in serpent it is populating everything so we might not require all these things two things you can do here you can load it and then again you can go to transform data here and then you can change or you can simply click here transform data and it will open your power query editor let me show you this option so for example I will click on transform data so what will happen is it will open the power query editor so you can see here it opened our power query editor and we have two queries here one is the product list and we will make the report from the product list so I will select from the queries product first so now if we look at here these are the columns if I will scroll and just little bit you can see here it is showing me all the columns here but we might not need all these columns for our reporting so what we can do here is if you will see here in the home tab there is it choose columns is there you can just select the first option choose columns and it will now populate all the columns you can see here simply uncheck everything just in the first tick box you can do that and then you can just select the columns which will be required for example in this case I require these columns and I will I also need the vendor column so I will only select these columns and then I will click on ok so once you click on ok now you can see here this is our or these many columns that we require first thing is if you'll see here I have the this is a vendor column and this is as I said this is a lookup column so I'll right click over here sorry you can just click over here and then it will select all the columns you can just uncheck and in this case also our column name is the title column which contains the name of these vendors just click on OK so now if you'll see here it is populating all our columns so this is how you can you can get this or the column names and the next thing is quite important is the data type that you can change if you'll see here you can see here we can change the data type so how we can change the direct I is a by default if you are using Excel source then it will convert the data type to the actual data type for example if this quantity maybe it will put the whole number price whole number or the currency sells date it will put it date field but when you are retrieve data from SharePoint list you might if the data will be any or the data type will be any you can see it here so it is showing any and also one more thing is when you are actually working with the number fields that is quite important that we will change it properly so that our report will come property properly it will be a whole number or something so in this case if we will see here title column title column should be a text column and you can see here we put the taste column here now this becomes a text column you can also select multiple columns for example I will select customer name city and the location and vendor this all these four columns should be a text column so I will select here and I will say text so now if we will see here all four columns become a column type text similarly I will select quantity price delivery charges probably these three columns I will make it to whole number so you can see here I will take whole number and the date column which is quite important as well you can select the date or date time I will select date here so you can see here now our data type got changed next thing what we can change is we can rename the column for example in this case if you'll see here this is a title column but this is the product name you can take so you can say select here and you can go to the Home tab and then you can see here go to the properties and then you can change it over here you can select this and you can change the data type by clicking over here sorry you can just right click and you can say rename you can see here you can do that apart from that I think in this transform tab you can see here the journey name column if rename button if you'll click on that then it will select and you can say product name something on it and here if you'll see the other one maybe the customer name I will put space and similarly on the sales date I will rename it maybe sales paste it and delivery charges I will rename it to delivery space charges and one more thing that we will change in the last one which is Englander dot title instead of that we may take vendor name just click on inter now if you'll see here our data formatting is done and then you can just go to the Home tab click on closing apply and you will see here now they apply the query changes will happen and then we will be landing off with our report term where we can add the visualizations so you can see here now we are if anytime you want to see the data you can go to the data tab so you can see here we are now in the report tab where we can add the visualizations remember this is the page from here we can change the page name you can see here down here it is showing the page title let me just minimize a little bit so you can see here this is the option where we can change the page name salary name and I will say product sales report and then this is the visualization section where we can add add our visualizations various visualizations are there they say do not chat is there you can see here I will just click on that and you can make it two little larger you can see here and then what we can do here is you can select the data or table from where we want to get the fields you can see here I select it and then I will select first thing is the product name the moment I select you can see here it came over here and you can also it is coming here then you can say selecting the values and then select the quantity let me just put the quantity here and you can see here it is showing the quantity here you can see if I'll just make it a little bigger here you can see now we can see here how it is appearing our product names and then the quantity that has been ordered for delivery and it is coming in different color you can change the colors you can change the font font also we will take that in another video and you can save the report you can just click over there and you can say a product I will make it to something meaningful for me so this is basically you can give a name for the product or for the report and this way you can save it so that next time if you are opening you can just double click on that file and it will open in our power bi desktop so this is how we can create a report from our SharePoint Online list and also we saw how we can do formatting and if you like our videos then subscribe to our YouTube channel you will see lot of videos on SharePoint office 365 or ba powerups flow SP effects all these technologies videos you will get as well as you can visit our sites so these are our websites these are our twitter handles LinkedIn Facebook and these pages you can like us and also if you are interested for the SharePoint training you can check the comment box there I will put a URL where you you can take out various training courses available in SharePoint thank you and have a nice day

Channel: EnjoySharePoint

Views: 8,181

Rating: 4.9506173 out of 5

Keywords: SharePoint Online, How to create Power bi report from SharePoint Online list, create power bi report from sharepoint list, create power bi report from sharepoint online list, how to create power bi report from sharepoint list, create a power bi report from a sharepoint list, power bi report on sharepoint list, power bi create report from sharepoint online list, change data type in power bi, change data type of column in power bi, rename column in power bi, power bi donut chart

Id: EFY3rvZ6VzA

Length: 12min 54sec (774 seconds)

Published: Thu Apr 09 2020