Microsoft PL-300 Certification for Power BI Data Analyst

Practice Activity Number 1

It's time for your first Practice Activity.

1. If you haven't already downloaded the workbook "PowerBIData", please do so from the Resources lecture in Section 1 of this course.

2. Open up Power Bi, and use this workbook to "Get Data" from the spreadsheet "PA - CDs" (not: "CDs", but "PA - CDs". This is a special version of the CDs spreadsheet for these Practical Activities).

3. Click on the fields "File extension" and Size.

4. Save this page to your local hard drive, and publish it to the Power BI service.

Good luck. If you get stuck, then please watch the next video, when I will do this Practice Activity as well.

Practice Activity Number 2

This Practice Activity continues from Practice Activity 1. As a reminder, you got data from the spreadsheet "PA - CDs" (from the workbook "PowerBIData") and added the fields "File extension" and Size.

In this practice activity:

1. Check the default summarization for the "Size" field.

2. Add the "Date created" field into the visualization as well. Put it after the "File extension" but before the "Size" field.

3. Has it created a detailed hierarchy? If so, click on "Date created" in the "Values" well and select "Date created" (instead of "Date hierarchy")

* If it hasn't, then your system may have auto date hierarchies switched off. To switch it on, go to File > Options and Settings > Options > Data Load (Current File) > Auto Date/Time.

4. Change the summarization for "Date created" to "Earliest", just for this visualization (don't change the default).

5. Change the default formatting of the "Date created" field so that it just shows month and year.

6. Change the font size of the visualization.

7. Change the color of the header background.

8. Change the format of the "Size" field so that it displays in Millions of bytes.

9. Copy this visualization to create a second visualization, and alter this second visualization so that it has just two columns: Year of "Date created", and "Size".

10. Move the first visualization so that the center of it is halfway down the page, and move the second visualization so that the top of the visualizations are the same.

Practice Activity Number 3

Using the "PA - CDs" spreadsheet in the "PowerBIData" workbook, please have a go at the following activity:

1. Create a Matrix, with Year of "Date created" in the Rows well, "File extension" in the Columns well, and "Size" in the Values well. Use the "alternating rows" style.

2. Change the formatting of the "Size" in this visualization only so that it displays in Millions.

3. Add "Quarter" into the Rows well. Have a look at the different options in Visual tools - Data/Drill - Data actions.

4. Do you have a problem with seeing some of the row headers? Please change the Font color of the Row Header.

5. Add a second visualization, a Stacked Column Chart, with "File extension" in the Axis well, and "Size" in the Value well.

6. Add a third visualization, a Stacked Area Chart, with Year and Quarter of "Date created" in the Axis well, and "Size" in the Value well. Then click on "Expand all down one level in the hierarchy" to show both Year and Quarter.

7. Edit the interactions, so that when you click on the first visualization, it filters (not highlights) the second visualization.

8. In the first visualization, click on the year 2010, and see what happens.

Practice Activity Number 4

Using the "PA - CDs" spreadsheet in the "PowerBIData" workbook, please have a go at the following activity:

1. In the first page, create a table, which summarises "Year of Date created" and Size. Duplicate this as a stacked column chart, and then again as a stacked column chart, but with "Month of Date created" instead of Year.

* Have a problem with this step? See the bottom of this Practice Activity.

2. Filter the table, so that it only shows those items/rows with the top 10 values by Size.

Hint - you can filter the # field to select "items", and then show the Top 10 "By Value" Size.

Note: this will only show 6 years, as some of the top 10 are in the same month.

3. In the table, add in the "#" field after Year and before Size. Don't summarise the "#" field (so at the end, you should have 10 rows plus the Total row in this table).

4. Sort the table in descending order of Size (so the biggest size goes on the top)

5. Add the Year of "Date created" as a slicer, and slice on the years 2010 to 2014. Create a bookmark based on this, but only for the "selected visual".

6. Slice on the years 2006 to 2009, and create a second bookmark based on this. Then slice on all the years, and create a third bookmark.

7. On a second page, create an area chart with "Date created: Year and Month" on the Axis, "File extension" on the Legend and "Size" on the values. Click on "Expand all down one level in the hierarchy". Rename this second page "Breakdown by month".

8. Add a Drill-through on this second page for "Date created: Year".

9. A "Back" arrow has appeared. Arrange the visualizations so that the Back arrow does not overlap a visualization, and add Button Text of "Back".

10. Go back to the first page (which you can rename "Dashboard") and the 2010-2014 bookmark. Drill through from the first page to "Breakdown by month" for the year 2011.

And why not experiment with making the dashboard look nice? How about changing the theme to Frontier, adding titles, formatting numbers, adding data labels and more?

Save this dashboard, as we will be developing it in Practice Activity 5.

* Do you not have a date hierarchy in step 1? Your system may have auto date hierarchies switched off. To switch it on, go to File > Options and Settings > Options > Data Load (Current File) > Auto Date/Time.