Import Data from SQL Server to PowerBI and Create PivotTable Report from Excel for the Web

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

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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *