Exercise - Excel and Power BI service better together
Overview
The estimated time to complete this exercise is 30 minutes.
In this exercise, you'll complete the following tasks:
Use Excel to create a pivot table from a Dataset published in the Power BI service
Add Sparkline Charts
Note
This exercise was created based on the sales activities of the fictitious Wi-Fi company called SureWi, which was provided by P3 Adaptive. The data is property of P3 Adaptive and has been shared with the purpose of demonstrating Excel and Power BI functionality with industry sample data. Any use of this data must include this attribution to P3 Adaptive. If you haven't already, download and extract the lab files from https://aka.ms/modern-analytics-labs into your C:\ANALYST-LABS folder.
Exercise 1: Use Excel to create a Pivot Table from a Data Set in the Power BI service
In this exercise, you'll use Excel to connect to a published Data Set in the Power BI service.
Task 1: Launch Excel
In this task, you'll launch a new blank worksheet to get started.
Launch Excel.
Note
If you haven't already signed in to your Microsoft 365 account, you may be prompted to sign in. Use your work email address and password to sign into your account.
Create a new blank workbook.
Task 2: Use Insert new Pivot Table from Power BI
In this task, you'll create a new Pivot Table workspace connected to a published data set in the Power BI service.
Select the Insert tab on the Main Excel main ribbon menu.
Choose PivotTable > From Power BI.
If you have many published Data Sets, you can use the Search option to type in "Lab", then select the MAIAD Lab 03A - Power BI Model data set from the available data set options.
Notice you have a new Pivot Table workspace and the PivotTable Fields with Measure and Field tables.
Note
Measure tables are identified by the summation icon. This behavior occurs when Excel connects to a Power BI dataset. All Measures that can only go into the Values of the PivotTable Fields will be in located in the summation icon table name. Any of the columns or calculated columns that are used for Filters, Columns, or Rows are located in a table icon of the same name.
Note
On the right-hand side of the PivotTable Fields window, notice the icons that allow you to toggle the window between Power BI Datasets or PivotTable Fields.
Task 3: Add Measures to the PivotTable Fields Values
In this task, you'll populate the PivotTable with Measure fields from the Power BI Dataset connection.
From the Contracts measure table, drag the Raw MRR per Office measure to the Values section in the PivotTable Fields List.
Task 4: Add Fields to the PivotTable Fields Rows
In this task, you'll populate the PivotTable with Lookup fields from the Power BI Dataset connection.
From the Offices field table, drag the Region and District fields to the Rows section in the PivotTable Fields List.
Note
Lookup field tables are identified by the table with Fields icon.
From the Dates field table, drag the Year field to the Columns section in the PivotTable Fields List.
Task 5: Add Slicers
In this task, you'll Add Slicers connected to the Pivot Table.
If the PivotTable Fields list doesn't display, select the Pivot Table to make it active, then right-click choose Show Field List.
In the PivotTable Fields, locate the Customers Lookup fields table and right-click the Company Size field.
Select Add as Slicer.
Note
The Slicer will just appear in a random location the worksheet. You'll reposition the Slicer in the next task.
You can also add a Slicer from the Main ribbon menu. Select the Pivot Table to make it active, then select the PivotTable Analyze tab in the Main ribbon menu.
Select the Insert Slicer button.
From the Customers Lookup fields table, check the box next to the Segment field.
Select OK.
Task 6: Move and Format Slicers
In this task, you'll insert blank Rows and Columns to make space for the Slicers to create a mindful report design for the end users.
Right-click Column A select Insert to add a blank column to the left of the Pivot Table.
Right-click Column A, select Column Width, enter 1 in the Column width field, and select OK.
Right-click Row 1 and select Insert to add a blank row above the Pivot Table.
Use CTRL + Y to repeat the insert five times to create a total of 6 blank rows above the Pivot Table. This provides space for our Slicers above the Pivot Table.
Drag the Company Size slicer above the Pivot Table.
With the Company Size slicer selected, choose the Slicer tab on the Main ribbon menu.
Change the Slicer buttons number of Columns to three.
Change the Slicer Style color to Dark Blue.
Drag the Segment slicer above the Pivot Table and to the right of the Company Size slicer.
With the Segment slicer selected, choose the Slicer tab in the Main ribbon menu.
Change the Slicer buttons number of Columns to 5.
Change the Color to Dark Blue.
Task 7: Add Report Title & Format Pivot Table
In this task, you'll add a title for the Report and apply final formatting to the Pivot Table.
In cell B2, enter the Report title MAIAD - Lab 03B - Excel & Power BI service - better together and press CTRL + B to make the font bold.
In cell B8, enter the Pivot Table row title By Region & District.
In cell C7, enter the Pivot Table column title By Year.
Select the Pivot Table to make it active.
Select Design from the Main ribbon menu, then select the Subtotals button, then select Show all Subtotals at Top of Group. This will create Subtotals for each Region in the Pivot Table.
Highlight Columns C to J, right-click, select Column Width, enter 12 in the Column width field, and select OK.
With the Columns C to J still highlighted, select the Center alignment from the Home tab on the Main ribbon menu.
Expand the Segment slicer so that each of the values is fully visible to the end user.
Right-click the Pivot Table and select PivotTable Options....
From the Layout & Format tab, uncheck the Autofit column widths on update box, then select OK.
Exercise 2: Add Sparklines
In this exercise, you'll create Sparkline charts to display the Year trend next to the Pivot Table.
Task 1: Create Sparkline chart
In this task, you'll create a Sparkline chart, combining features in Excel with a Data Model published to the Power BI service.
Position your cursor in cell K9, select the Insert tab at the top of the screen, then select the Line button.
Enter D9:H9 in the Data Range field to specify the Central Region and the Years 2013 to 2017, then select the OK button.
From the Sparkline menu options, select the Sparkline Color drop-down and select Green, Accent 6.
From the Sparkline menu options, select the Marker Color drop-down, select High Point, then select Green, Accent 6. Then select the Marker Color drop-down again, select Low Point, and select Dark Red.
In Column K, copy and paste the Sparkline for each of the Region rows.
In cell K8, enter the title Completed Year Trend.
Hold the CTRL key and highlight each of the Region rows (9, 13, 16, 19, and 23), right-click, select Row Height, enter 20 in the Row height field, and select OK.
Task 2: Final Formatting
In this task, you'll create a final polished report by removing the Excel headings and gridlines.
From the main ribbon menu, select the View tab.
Uncheck the Formula Bar, Headings, and Gridlines boxes.
Task 3: Save the Excel file
In this task, you'll save the Excel file.
From the main ribbon menu, select File > Save As.
Navigate to the C:\ANALYST-LABS\Lab 03A folder.
Save the file as MAIAD Lab 03 - Solution.xlsx.
In this exercise, you started in the Excel application and connected to a published data set in the Power BI service to create a Pivot Table with Slicers and Sparkline charts, demonstrating how Excel plus Power BI is better together.
