Power BI is Powerful! My Data Analytics Learning Journey
Learning Power BI through Udemy “Microsoft Power BI Desktop for Business Intelligence” Course
Before We Jump In…
It has been about a month since I started my data analytics journey. I started off knowing nothing about data analytics. I didn’t know what SQL, Power BI, Tableau, R, were, not to mention I didn’t even know data analytics was a thing.
I started my learning journey with the Google Data Analytics Professional Certificate. I completed this course and it gave me a good understanding of what Data Analytics is about and what is needed to be a good Data Analyst. Using what I have learned from the course, I completed one of the case studies which was part of the course. The case study portfolio can be found here.
Even though I thoroughly completed the course and learned different skills, I knew I need to learn more in order to understand deeper into the system and concepts, be confident and competent to use the right tools and skills for the right tasks.
So, I started to look into learning options and I found Udemy. I completed 5 different courses on Excel, SQL, Tableau, and I just completed Power BI.
Introduction
I will guide you through my learning journey on Power BI. I completed the Udemy course on “Microsoft Power BI Desktop for Business Intelligence” by Maven Analytics. This post will not contain every step of the course nor discuss the deep principles of data analytics and visualization, rather it will highlight significant learning points, formulas, or concepts.
Before we go further, if you are wondering what PowerBI is, you can visit here.
My Learning Started Here
Table Transformation / Data Normalization
The first step of Power BI is uploading the data (in my case I had .csv files), and giving tables meaningful and relevant names. The important learning here was the differentiation of the types of tables. There are two types when you model the database: data table and lookup table. Data table often consists of numeric values that aren’t necessarily self-evident by themselves and are without contexts like product code, id, or customer key. Data tables also consist of multiple foreign keys. Lookup tables, on the other hand, contain descriptive data such as an address, customer name, and product name. They also have their own unique primary key.
As I uploaded file after file, I cleaned the data in the Power Query Editor. I learned about:
setting up headers, chaning data type, split/merge, transform column/add columns, pivoting/unpivoting columns, conditional column, formatting, extracting, calendar rolling, indexing column, appending queries, defining hierarchies, statistics, standard, rounding, group by, using applied steps, and etc.
Table Relationship
Once data is uploaded and cleaned, the next step is creating table relationships.
There are two ways to create table relationships. One is simply dragging columns to the matching columns. Another way to do it is in the ‘manage relationship’ section. It is easier to do it by dragging, however, I can use more specific features like two ways filter flow in the manage relationship section.
There is a so-called snowflake relationship which refers to the data model with chains of dimension tables, for example, Category_Lookup -> Subcategory_Lookup -> Data Table.
Also, it is important to hide foreign keys so as to use the primary keys when working at the report board later.
Data Analysis Expression (DAX)
Once data is cleaned, normalized, and structured within table relationships, it is time to create some “Data Analysis Expression (DAX)”
DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.
The row data we have in the table can be transformed into more meaningful information and data through DAX. For example, with product retail prices and order quantity, which are both row data, I can create revenue data. Or, by subtracting product cost from product retail prices, I can create profit data. Lastly, by diving revenue by profit, I can create profit margin data.
Total Revenue = SUMX(MM_Transactions, [quantity] * RELATED(MM_Products_Lookup[product_retail_price]))
Total_Profit = [total revenue] — [total_cost]
Profit_Margin = [total_profit]/[total revenue]
These are only basic examples. I will put a few more formulas and calculations down below.
Now, important concepts to point out here are the “calculated columns” and “Measure”. Calculated columns are columns that have been calculated and located and can be shown in the table as a column, in fact, it is a part of the table. On the other hand, “measure” is to create numerical, calculated values that can only be analyzed in the “values” field of a report visual.
Examples of calculated columns:
discount price = MM_Products_Lookup[product_retail_price] * 0.9
profit = MM_Products_Lookup[product_retail_price] — MM_Products_Lookup[product_cost]
Examples of measure:
Total Revenue = SUMX(MM_Transactions, [quantity] * RELATED(MM_Products_Lookup[product_retail_price]))
Total_Profit = [total revenue] — [total_cost]
Like this, an analyst can produce a variety of advanced data/information using DAX.
Common Functions:
Math&Stats: SUM/AVERAGE/MAX/MIN/DIVIDE/COUNT/COUNTA/COUNTROWS/DISTINCTCOUNT
Logical: IF/IFERROR/AND/OR/NOT/SWITCH/TRUE/FALSE
Text: CONCATENATE/FORMAT/LEFT/RIGHT/MID/UPPER/LOWER/LEN/PROPER/SEARCH/FIND/ REPLACE/ SUBSTITUTE/TRIM
Filterː CALCULATE/FILTER/RELATED/
Date&Time: DATEDIFF/WEEKDAY/WEENEND/YEAR/MONTH
Advanced DAX Examples
The following are some of the formulas used for this course that I think are relevant for analysis:
Quantity Returned = SUM(AW_Returns[ReturnQuantity])
Return Rate = [Quality Returned] / [Total Sold]
BulkOrders = CALCULATE([Total Orders], ‘AW_Sales_2015–2017’[OrderQuantity] > 1)
Overall Avg Price = CALCULATE([Avg Retail Price], ALL(AW_Product_Lookup))
High Ticket Order = CALCULATE([Total Orders], filter(AW_Product_Lookup, AW_Product_Lookup[ProductPrice] > [Overall Avg Price]))
YTD revenue = CALCULATE([Total Revenue], DATESYTD(MM_Calendar_Lookup[date]))
60-day revenue = CALCULATE([Total Revenue], DATESINPERIOD(MM_Calendar_Lookup[date], MAX(MM_Calendar_Lookup[date]),-60,DAY))
last month transaction = CALCULATE(MM_Transactions[total_transaction], DATEADD(MM_Calendar_Lookup[date],-1,MONTH))
last month revenue = CALCULATE([total revenue], DATEADD(MM_Calendar_Lookup[date],-1,MONTH))
revenue target = [last month revenue] * 1.05
Report / Visualization
Having row data transformed into more meaningful information, I was able to visualize a variety of data information.
This report was the course project. So, I created this report by following the course step by step. This report contains different features and visualization tools. This page alone has slicer(timeline), treemap, matrix, bar chart, KPI, card, map, and bookmark. These visualizations all interact with each other, meaning that if data was changed in one visualization tool, the other tools will also change accordingly.
For example, if I select only Europe in the continent slicer, the data in the other visualizations will be filtered to show the data that is exclusive to Europe.
The next page shows product details.
This page shows one chosen item’s data, as shown on the top left(Water Bottle — 30 oz). This page has features including KPI (order, revenue, return targets), Price adjustment slicer, line chart (including forecasting), and area chart. Moreover, it has the “drill-through” feature so that I can choose what product will be displayed here from the Exec Summary page.
Lastly, there is the customer detail page. This page consists of matrix, pie chart, line and clustered column chart, treemap, and cards that show top customer with their orders and revenue.
This report was a bonus project of the course. This was done independently from scratch, applying and implementing what I have learned from the whole course.
As I finish the course
It was a great learning opportunity. Through this course, I got to learn more about data cleaning, data modeling, data normalizing, table relationship, DAX, and a variety of skills and features on visualization. The course was easy to follow, yet challenging enough to make myself work hard to understand the concept and actually apply it in practice. It consisted of mostly hands-on lectures which made it easy for me to complete the bonus project independently. Power BI is fun and useful!
Thank you for reading.
Jeff
