Before we begin let us prepare the spreadsheet that we want to upload to Power BI first. I am going to use AdventureWorksDW sample database for this demonstration
Open Excel click on Power Query -> From SQL Server Database
Type the server name and your database name. The SQL used is below if you want to follow along.
SELECT c.EnglishEducation AS [Customer Education Level], g.EnglishCountryRegionName [Customer Country], CASE WHEN c.HouseOwnerFlag = 0 THEN 'No' ELSE 'Yes' END AS [House Owner], Count(*) AS [Internet Order Quantity], Round(Sum(f.SalesAmount), 2) AS [Internet Sales Amount] FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] c ON f.CustomerKey = c.CustomerKey INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] g ON c.GeographyKey = g.GeographyKey GROUP BY c.EnglishEducation, c.HouseOwnerFlag, g.EnglishCountryRegionName ORDER BY c.EnglishEducation, c.HouseOwnerFlag;
By default the Load to Data Model check box is uncheck. You need to check it since we will be uploading to Power BI otherwise you will get an error while enabling it in Power BI. I will show you that in the later steps.
Now click on Apply & Close button and save the spreadsheet.
Next let us add a Power View dashboard to our spreadsheet. Click on Power View from Insert Menu
If this is the first time it will ask you to enable the add-in and go ahead enable it.
Now you get the Power View window.
Since we plan to create a dashboard let us convert our table to a chart. Click on the table then simply click on one of the chart icons.
Now we get our data converted to column chart. Okay this is a dash board and I would like to have a slicers and be able to add more dashboard items. To add slicer simply click on the filter button and to add another chart you don’t need to add another data set simply copy the original chart and paste
Then we could modify the two items such as one of them to work with the customer country and the other one with the customer education level.
Finally, we are ready to upload. Save and close the worksheet
1, Add a Power BI site app if you haven’t done so yet
2, Click on Power BI link in the left
3, Click on Add and select Upload file
4, Next use the ellipsis on the spreadsheet then select enable.
If your spreadsheet doesn’t have data model you will end up with an error message like this one
But if it does have one it will be enabled
Now if you click on the new worksheet we uploaded the same dashboard in cloud.
The other method instead of uploading the document on Power BI site we could simply save the document to our Office 365 SharePoint document library. T do that find your document library address https://[siteName].sharepoint.com/Shared%20Documents then from File menus save the document the same way as you save any file on your machine.
I modified the previous worksheet and I would like to save it directly to Power BI SharePoint document store
2, Next enter your user email and password to add your SharePoint location.
3, Once you finished the previous steps your SharePoint location will appear click on that and Browse
4, Give a name for your document and click on Save.
Once we finish saving on SharePoint document library if we go back to the actual site we should be able to find the document there.
It still require to be enabled and go ahead and enable it by clicking on the three dots
After it is enabled we could be able to interact with same way as other documents we uploaded from the Power BI site.
In the next blogs we will discuss about Power BI mobile app, setting up data management gateways and on how to set up automatic data refresh for Power BI reports from on-prem data source so that our business users could have self service BI in cloud without a need to wait for an IT team to prepare a report everyday.