Welcome to Excel Jet Consult blog post. In this blog post, I will cover how to “Import Data from SQL Server to PowerBI and Create PivotTable Report from Excel on the Web PowerBI.” Let’s dive in
SQL Server Data.
Our dataset are the three tables under the SalesData database. I have written simple select all queries to retrieve the data from the tables as seen below
PowerBI SQL Server Data Connector
Next, we switch to PowerBI Desktop. We want to connect to the dataset in the database. To do that:
- In the Data group, click on SQL Server
- In the SQL Server database dialogue box, input the Server details. For this, I have entered by details as seen below, click on OK
Next, in the Navigator dialogue box, we can see the Database name and the three tables retrieved earlier.
- Check the boxes as seen below
- Click on Transform. This would load the tables to the Power Query editor for cleaning and transformation if required
From the above screen, I have transformed the Order Date & Ship Date Data Type from Date/Time to Date.
- Next, click on Close & Apply. This steps closes the Power Query and Apply to the Data Model – PowerBI
Automatic One to Many Relationship
From the screenshot below, we can see the automatic one to many relationship from the fact table and the dimension tables
In additional, we have created two simple DAX Measures using SUM & COUNT DAX aggregate functions
Building Report
Next, we want to build our simple report to show Total Sales & Total Orders by Account Manager. So, we need Table vix
- Drag and Drop Account Manager under DimAcctManager table to the report Canvas
- Drag and Drop Total Sales & Total Orders to the Table where Account Manager is
The above will create Table Report
To create our Clustered Bar Chart
- Click on Bar Chart viz
- Drag and Drop Total Sales and Account Manager fields into the ghost bar chart vix
- Finally, to create our Slicer, click on Slicer viz
- Drag and Drop Customer Type field
All the above steps will create the report as seen in the caption below
Next, we needs to save the report and click on Publish. As seen in the caption below, we have published to the PowerBI service
Next, go to app.powerbi.com.
Locate workspace. You should find the report there
Excel for the Web: PivotTable from PowerBI Dataset
Finally, go to Excel for the Web and create a blank sheet
- In the Insert tab, click on PivotTable dropdown and click on From PowerBI Dataset.
- Drag and drop Total Sales and Total Orders to Values area
- Drag and Drop Account Manager to the Rows area. This would create the report as seen below
See you in the next tutorial