MS Power BI
Text

Power BI - Gladiator Academy - One of Five Modules

11 Lessons Easy

About this course

​Workshop Overview

SCENARIO

Joe Joe’s Restaurants is one part of ​fictitious hospitality conglomerate group in the UK. The group operate a national chain of restaurants, hotels, and private dining clubs selling, Asian cuisines, Fusion cuisine, Haute cuisine, Nouvelle cuisine, Note by Note cuisine, Oceanic cuisine, Vegan cuisine, Vegetarian cuisine.

The groups London office stores the business unit’s sales data on an Access database. Business unit’s home nation sales transactions are available as comma separated (CSV) files. Some business units generate their sales data manually others do so through automated processes. Each business unit is responsible for sending their files. These files are held in dedicated folder. To make things as easy as possible these CSV files have the same column structures as the sales table held on a SQL database managed by London head office. 

You have been set the task of analysing Joe Joe’s national sales data for the year 2000 to 2015. Your first step is to bring the data into Power BI Desktop. The next step is to compare Joe Joe’s home countries sales with the home countries population. To achieve this, you will need to import the country population data from a less structured Excel report to Power BI.

​WORKSHOP OVERVIEW

This workshop comprises of three exercises:

1. Import data to Power BI Desktop from an Access database file.

2. Import data from CSV files which resides in a file folder then append the fresh data to the corresponding existing data that originates from the Access Database.

3. Import from an Excel file country population data to Power BI Desktop.

Before starting this workshop, review the module if you feel you need to. Next you will need to setup the workshop environment for this section.

WHAT’S REQUIRED

A computer with the latest version of Power BI Desktop installed on it.

A copy of the Access Database containing Joe Joe’s sales data.

Home Nations folder with 4 CSV files, containing Joe Joe’s home UK national sales data:

England Sales.csv

Northern Ireland Sales.csv

Scotland Sales.csv

Wales Sales.csv

An Excel file containing country population data.

Connecting to an Access Database for the first time

If you are connecting to an Access database for the first time, you might need to install the Access redistributable package https://www.microsoft.com/en-us/download/details.aspx?id=54920

If you have a 32-bit machine, you need to install the 32-bit Power BI Desktop https://powerbi.microsoft.com/en-us/desktop/   and the 32-bit redistributable of Access.

If you have a 32-bit Office installed (regardless of your machine), you need to install the 32-bit Power BI Desktop and the 32-bit redistributable of Access.

Otherwise, you can install the 64-bit Power BI Desktop and the 64-bit redistributable of Access.

Follow the link provided by Power BI Desktop when trying to connect to the Access database.

​​Exercise 1: Import Data from Access Database

Joe Joe’s office stores the sales data on an Access database. You have been tasked to perform analysis on that data, but before you can begin, you need to import the data to Power BI Desktop and perform some transformations.

IMPORTANT! Before you commence, if your locale settings have not been set for English (United Kingdom), you might want to change this, since the data you will import is based on this locale.

You can change your Power BI locale settings by going to File, Options & Settings, and select Options. Select Regional Settings under the Current File list, and set the Locale to English (United Kingdom) see below.

Options Power BI

Download the Module 1 Data ZIP file

NOTE: Direct link is a to Microsoft One drive share

https://1drv.ms/u/s!AjlmEEOEDXwclWHNJa8lDYijRMAD The data is FICTITIOUS and for educational training purposes ONLY .

Start with a blank Power BI Desktop file.

Connect to the Access database by using Get Data and select the Access database file.

Select the following tables to be imported: bi_date, bi_geo, bi_hospitality, bi_product, and bi_salesFact.

Next edit the query before loading to the data model.

Filter the rows on the bi salesFact query to include dates from January 1st, 2000. (Hint: Apply a Date filter in the Date column to import dates after December 31st, 1999. Before you can apply a date filter, you need to change the Date Column’s Data Type to Date).

Filter the rows on the bi_date query to include dates from January 1st, 2000. (Hint: Apply a Date filter in the Date column to import dates after December 31st, 1999. Before you can apply a date filter, you need to change the Date Column’s Data Type to Date).

Rename the queries as follows:

bi_date: Date

bi_geo: Locations

bi_product: Products

bi_salesFact: Sales

Load the data into Power BI Desktop. This might take a few minutes.

Explore the imported data in the Data View.

​W​ORKSHOP QUESTIONS

​​Exercise 2: Import Data from a Folder Containing CSV Files

Continuing from the previous exercise. You need to create a Query for the home nations sales and append the Query to the Query from current Sales.

If you haven’t already done so download the zip file containing home nations sales data and extract it to a folder. You should see 4 CSV files in the folder.

Import the data from the file folder by using the Get Data. To do this, click the Get Data icon / in the drop down select More option, select Folder and click Connect. Select the folder where you saved the 4 CSV files containing home nations sales data.

Perform the following steps:

Select to the Edit button the content of those 4 files.

Filter the rows that come from the header of the CSV files. (Hint: Left-click the content column to make that your active choice then right-click and choose to remove other columns).

Next left-click the double headed arrows in the content column header (loads content from the files. Click OK to the following window). You can skip this step by clicking combine and edit button rather than edit button. 

In the country column click the arrow header and left-click load more and remove the tick for blank and click OK.

Filter the rows that are after December 31st, 1999.

Click the Sales Query to make this your active choice.

Append the Home Nations Sales Query to the Sales query.

In the Sales query, add a custom column named Country Name which takes the value of the Country column when it is not null and the value of "England" when the Country column is null.

Remove the Country column.

Load the data into Power BI Desktop. This might take a few minutes.

Explore the imported data in the Data View.

Hide the International Sales table from report view.

​WORKSHOP QUESTIONS

​​Exercise 3: Import a Less Structured Data from an Excel File

Continue with your Power BI file from the previous exercise. You want to import an Excel report containing population data for the countries that Joe Joe’s operates.

If you haven’t already done so download the populations zip file containing the Country Population data. (Data is a subset of the "Office of National Statistics, People Population Community" dataset).

Import the data from the file folder by using the Get Data. To do this, click the Get Data / Excel option. Select the Excel file containing the Country Population data.

Edit the query and perform the following steps:

Name the Query Country Population.

Remove the first ten rows of the table and the null rows beneath then remove empty or null columns. You should be left with just the data.

Promote the row that represents the title of the column to the table headers.

Transform the yearly columns to rows and name the resulting columns appropriately. (Hint: You might find the Unpivot Columns feature useful).

Rename the resulting columns to Year and Population respectively.

Filter out the year 1999.

Set the Data Type of both the Year and Population columns to Whole Number.

Load the data into Power BI Desktop.

Explore the imported data in the Data View.

​WORKSHOP QUESTIONS

Course Structure

ONLY FOR LOGGED-IN USERS

Start Here!

Introduction

ONLY FOR LOGGED-IN USERS

Software you need

Obviously you do other wise you wouldn't be viewing this course.

ONLY FOR LOGGED-IN USERS

Synopsis of Power BI

High level overview of Power BI process.

ONLY FOR LOGGED-IN USERS

How to connect to Software Services

Helicopter overview of connecting to data services for example Adobe, Google Analytics, Acumatica, MailChimp, SQL to Power BI interrogating the data and publish reports & dashboards.

ONLY FOR LOGGED-IN USERS

Power BI Desktop Modelling & Data Modelling in Power BI Desktop

Power BI Desktop can connect to all sorts of data sources and then help you, to extract, load and transform the data into a format you can start to build relationships in turn enable you to analysis and visualise the data.  

ONLY FOR LOGGED-IN USERS

How to Connect to a Access Database

This video demonstrates how to use Power BI Desktop to connect to a database

ONLY FOR LOGGED-IN USERS

Data transformation

In today’s world sustained competitive advantage at its core necessitates data transformation. Master this essential skill and you will remain one step ahead of your competitors.

ONLY FOR LOGGED-IN USERS

Connecting to files in a folder

Assume for a moment. You have a number of businesses operating in different geographic locations. Each month or week they submit sales reports to your offices. The data needs to be added to your database. This video illustrates how to append such files to your data set.  

ONLY FOR LOGGED-IN USERS

Less Structured Data

In this presentation, the focus is upon a more complex real-world example, for example, exporting a matrix-like data format and transforming the data layout into a column format a style that works well for Power BI.

ONLY FOR LOGGED-IN USERS

Create tables within Power BI

Create tables within Power BI. Manually type entries or copy and paste data then transform and query data to generate spectacular eye-catching reports. 

ONLY FOR LOGGED-IN USERS

Workshop

The workshop is an important opportunity for you to confirm what you do or don’t understand. Mistakes are often a better learning experience than getting everything right the first time so if you do make mistakes no one needs to know. On the positive side, you are unlikely to repeat your error. Good luck.

Powered by Thrive Apprentice
Pen
>