Please log-in to view this video. Sign up for FREE ACCESS HERE

Watch this insightful How To video on How to Integrate Power BI with SharePoint Online by trainer and consultant Arthur Graus.
Arthur advises companies how to get the most out of their SharePoint installation using a mix of custom development and SQL Server BI.

Video Transcript:

hi Arthur Graus here welcome to this how-to video in this video I’m going to tell you how you can integrate Power BI with SharePoint align if you want to create a dashboard and Power BI you need to take five steps the first step is collecting all the data that you need in your dashboards like data from the internet data from a sequel server table data from excel sheets CSV files or like we’re going to do this demo take some data from SharePoint Online the next step is creating a model in this model you’re going to relate the tables that you imported in step 1 and create relationships the other thing you’re going to do is step 2 is create formulas using Dex to create calculations likesome average accounts the next step is creating a nice dashboard with the Power BI desktop and if you will have a nice-looking edge wood created share it with your colleagues in step 4where you’re going to publish the report to the Power BI cloud service and then I step 5 you can consume the dashboard using the Power BI F or like we’re going to do in this demo you can insert the dashboard in SharePoint ID let’s get started with step one I created a list in SharePoint Online in which I have collected all my stock portfolio for example I have stocks and HTML Heineken royal dutch/shell with a quantity and a Restorick price that I’ve paid at the moment that I bought the stocks I want to combine this information in beige boards with the real life stock quotes in step one and Power BI I’m going to import these two sets of data and instep two and cannot create a relationship between those two these two tables let’s switch over to Power BI desktop I’m going to start with a SharePoint list so I click on the ribbon get the data the SharePoint data’s not here in this menu immediately so I click on the more button and if I scroll down i’ll find here the SharePoint online list that’s what I will use connect I’m going to paste in the Europe write that the list resides in okay now here we see a table of contents of the SharePoint site and I also see that portfolio stocks list I’m going to click it and you see all the data and all the columns that is in this SharePoint list I’m not using all these columns and click on edit button to make some changes and don’t need all these technical columns so I’m going to select all the columns up till content type ID with the shift pressed right mouse click and here I can remove the columns there F my title quantity store price that’s for the needs and all these out of columns I’m also going to remove columns for nowadays too what a neat I’m going to close and apply now I’m going to get the stock the life scope stock quotes from Internet I’m going to choose get data and here I can choose the width to get some data directly from Internet by page I’m going to copy the URL of the sides copy I’m going to paste it in here but power we are does it looks at the internet page and tries to determine if there are some data on the page and it has found five stables if I click on table zero it’s no more the need is an email address and a password if I click on table one that’s the thing I need I need to have the stock with the quotes but there are also some extra columns that I don’t need and this column has some data I want to strip off the zero of six general is not that I need so I’m going to click on edit and I’m entering a query editor again the columns are don’t need it can just put them like I did before with the shift button right mouse and then remove columns and this column along want to split it with the right mouse click I can use the split column transformation and I’m going to split by a delimiter in this case I want to split by a space and I only want to use the left most delimiter everything after the left more space I want to remove okay so now I’ll get two columns and the last column I can remove and now can rename these columns as I like quotes and I can save closing applying I can also want to rename this table so can do that here these are the like the stock quotes step one is now finished I’ve imported the data that I need now is the second step is to create a model I can do this by clicking on the relationships with them so here we are at the relationships panel I want to create a relationship between these two tables so I’m going to have to look for columns that are the same for example here is a title column and the other table has instruments which means this the stock I’m looking at these two columns I can drag them to each other and our create a relationship this case is one on one relationship meaning if I haven’t won my portfolio a stock with a certain title its unique in my portfolio and it also has one stock quote on the internet if for example I would be looking to add historic prices and I would get to sort quotes from the internet then it would be one-to-many relationship because I would have a quote for each day or each month ok let’s create a dashboard and let’s go to step 3 in this case I’m going to choose a table visualization you can click on here and now can drag fields from both tables onto this tablet’s start with the title and I also want to see the quantity and I want to see the clue but you see now is that I’ve got a lot of quotes with no title the title is empty because I don’t have this quote in my portfolio so it cannot find a title for it I don’t want to see these stocks so I’m gonna filter them out I can do this with the visual level filters I can click on the quantity I can select all of them and then deselect the blank so it is not linked so now only get my put Foley air quotes I also want to see this third price the next thing I want to do is do is a simple calculation I also want to see how climate for example behind again the stored price was 80 and now it’s at 77 so I made a loss if you look at a SML it was historic price that I bought it is 50 and now it’s at 114 so I made a profit I can do this for Dex formulas if I go to the portfolio stock with the right mouse click I can create a new column for example profits equals I’m going to use the quantity at times and I want to look up their current quotes and I want to subtract from that historic price but the current curve is need not in this table so I have to look it up in the other table that’s where you use the related function for so I can take related and then I can look up with for example the quote and then I’m going to subtract from that list or price and this profit column I can also drag it on the table it’s not so good to see so I’m going to make it a little bit bigger that’s where you can use this formatting button for here in the general I can increase the text size now it’s better readable and the other thing I want to do is make some nice coloring so if I have mega profits that it I want to make it green if I make a loss then I want to make it red and I also want to have a sifter though for example I want to use the summarization zoom so here I see my total profit and I can also you with the right mouse button I can use conditional formatting to create some colors I’m going to use numbers here for example if it’s lower than minus one I want to give it the color red and if it’s bigger than plus one and we’re going to give it to the color green okay we’re going to ask the title and then I’m finished with the dashboards I can click near textbooks and here I can create my stock dashboard okay now I finished my dashboard and I want to publish it to the Power BI cloud service that’s step 4 and then I want to embed it into SharePoint I can put this button publish yes I want to save I there I can choose a workspace work space is an environment in Power BI where it can work together with other people in this case I use Power BI Academy I’m going to click on this link to show the dashboards and that’s how it looks like in the Power BI cloud service if we want to embed this dashboard into SharePoint I can take the whole URL and I can copy it now go back to share points for example here on this dashboard demo page I want to insert my stop dashboard I can edit the page with edit button this is a new modern page that I created and on this new modern pages we can add the Power BI web part I can click on this plus button and if I scroll down you’ll see here the Power BI preview web part and I can click on add reports and I can then I can paste in your Power BI report link that I copied just down and you see our stock dashboards inside of SharePoint I click on save and close and we have our stock dashboard embedded in the SharePoint Online one page thank you for watching this how-to video I hope to see you again