Once you have selected your data source in a new session on Power Bi desktop application, load data into power query to transform it.
In case of a mutli-sheet workbook in Excel, select sheet(s) you intend to work with. They will eventually be made into tables in Power Bi. Then click on transform data.
Ensure to check for inconsistent data from column filters. Standardize any inconsistent data as well as blanks and pseudo-blanks. Using the column filter we are able to see categories for each column at a glance. This would help identify blanks, inconsistencies and missing values.
Click close and apply to load data into Power Bi.
Select the sheet from data pane and right click to rename it into something meaningful. Remember to save progress in location of our choice to avoid data loss.
1. Total sales from all products.
Since we already have sales for each individual product, we simply use SUM function to get the total for the SalesAmount column. Click on New Measure, give it a descriptive name and enter the function:
2. Profit Margin
Profit margin is calculated by profit/SalesAmount * 100. Since we already have the total value for sales amount calculated above, we will use SUMX to calculate the profit. Our new expression will be as shown:
The SUMX above encloses the profit for each row. It calculates profit for each row and also a sum at the end. For each row, this value is used to calculate the profit margin.
This will allow us to filter by country, product in the report view.
3. Change currency to USD
Currency is changed to USD from CAD for all columns that have currency using the formula below.
Shipping cost to USD
Sales Amount to USD
Profit to USD
4. Missing or incorrect values
Missing data values can be standardized as pseudo blanks for text columns or to be null for numbers.
In addressing missing numeric values, median is the best option as it accounts for skewed data. Mode is recommended for text, categorical or nominal categorical data. Mean is advised for data with normal distributed data.
5. Model data
Modelling our data involves creating dimensions tables and a fact table. Click on Transform data in Power Bi to open Power Query. Duplicate the existing table to the number of total tables (dimension tables and fact tables) needed.
Except for the fact table, columns not needed in each dimension table are deleted. Columns in the fact table are kept as is to use in the subsequent steps of creating unique identifiers for all dimension tables.
Click on Close & Apply to effect the changes and return to Power Bi.
Tip: Always keep a copy of the original table/data.
Date Table
To create a date table, click on the New Table command in Power Bi.
Enter the formula below to create the table:
CALENDARAUTO() generate a new date column based on all the dates in our data. To limit the use of possible unnecessary date columns that might exist in our data, eg. Date of Birth, we create new variables and determine the range our date should fall in. In this example, the MinDate looks for the earliest date an order was placed and the MaxDate returns the most recent date for delivery.
The filter is then used to give CALENDARAUTO() the date range to filter from based on these two values.
The other Date-related columns are derived from the date column returned by CALENDARAUTO() function.
Here is a view of our tables after the steps.
Creating unique identifiers
All our dimension tables will need unique identifiers to be linked to the fact table.
1. Create Unique ID in the dimension table
Click CustomerEmail from the customers table and select Remove Duplicates from the drop down.
Unique email identifiers remain.
From the Add Column tab, click on Index Column and the initiator of choice, in this case, Custom.
Define the desired index format and increment and click OK.
I rename the column to CustomerID, this is what we will use to join it in the fact table. I also change the type to text. This just as a personal preference for columns that eventually may include a lot of records.
NOTE: REPEAT THIS PROCESS FOR THE OTHER DIMENSION TABLES
2. Merge the Fields from the tables
Still in the Power Query editor, click on the fact table and select Merge Queries from the ribbon.
Select Left Outer Join with the fact table as the first table. You see from the image below we get all records matching. Click ok.
Click on the expand tables icon.
Deselect all column names except the CustomerID. Our new fact table will have this column included.
Our CustomerID is now included in the fact table.
Repeat for the other dimension tables.
The Relationship and view should be as shown
6. Report and Dashboard
Go to the report view and select visualization build from the side panel as needed. Add your key overview metrics from the different values. This will from the dashboard as shown.
Save your work and click on publish. Log in to a Power Bi account from the Power Bi Service online and select the option to create a workspace.
Give the workspace a name. Log in to the Power Bi account from your desktop application and click on publish. Choose the same workspace created earlier to publish the report to. You have now published your report.
Create a new page on the Power Bi desktop application and give it a relevant name for specific metrics to be shown.
This will form the individual reports of your work.
The published work should reflect on the online Power Bi service.
Embedding
To embed the report in another online document such as a webpage, click on File then select the Embed report then Website or portal.
Copy the resulting iFrame for embedding inside a html document.
Your Power Bi Report page will appear on the html document.
