How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

#analytics #data #microsoft #tutorial

Messy data no longer has to be scary! Think of it as a blessing in disguise; during the clean-up of the data, you get opportunities to interact with and understand the data. Therefore, you can easily come up with suitable dimension tables and fact tables from your data, which is helpful during modelling.

Getting data on Power BI

The first course of action will always be loading your data to Power BI. Power BI has over 150 default connectors, i.e., files (CSV/Text, MS Excel, JSON, etc.), databases (SQL, PostgreSQL, MySQL, Oracle, etc.), online services, Azure and many more.
Needless to say, the fact that Power BI can access data from multiple connectors means it could get frustrating for a beginner to load new data, especially if they are not sure of the data source and/or type.

Best practice when adding data

Open Power BI > Blank Report
Select 'Import data from Excel' > Change file type to 'all files', as in the image below
Click open > Select data tables that you want to see on your report > Load/Transform data

Getting additional data from other files/sources:

When analysing data from different files and sources, it is easy to add the second dataset, as there is no provision to load both datasets at once.

On the same report containing the data that was loaded on Power BI, you will need to:

On the Home Tab > Get data > More > Select 'All' > Connect
Proceed to follow the rest of the steps as if adding new data

Transforming data

Transforming data is the term used to indicate cleaning messy data on Power BI. Once you load new datasets on Power BI, it will automatically identify and categorize the uploaded data, like in the image below:

Disclaimer: The newly formatted data is not always in a format suitable for analysis. Therefore, one will always need to modify it and these modifications can only be done using the application known as *Power Query Editor *

Power Query Editor

You can initiate the Power Query Editor window while on either Report, Model or Table views on Power BI. On either view, navigate to the Home tab > Transform data

You get a pop-up window like shown below containing the data tables uploaded earlier

Power Query Editor Ribbon

The Ribbon has multiple tabs that will help with commands used for transforming data:

File Tab - Contains commands that are related to managing the working file on the Power Query Environment
Home Tab - Contains commands used in data preparation and organization, such as merging queries, changing data types, etc.
Transform Tab - Contains commands which help modify existing columns, such as grouping, pivoting, splitting and merging texts and columns, etc.
Add Column Tab - Contains commands for creating new columns derived from existing data columns
View Tab - Contains the commands controlling the visibility of panes and profiling tools to validate and inspect data quality
Tools Tab - Contains commands which offer diagnostic help options for troubleshooting queries
Help Tab - Provides access to documentation, learning resources, and support for Power Query

# Transforming messy data on Power BI

The importance of data transformation is:

Data cleaning and quality: Data transformation identifies and resolves errors, missing values, and inconsistencies, which increases the trustworthiness of reports.
Structuring for analysis: Transformations help format data correctly (e.g., changing data types, pivoting/unpivoting) so it can be effectively used in visualisations.
Performance optimization: Unnecessary columns or rows are removed, reducing data volume and enhancing report load speeds.
Automations: Repetitive, manual data cleaning tasks in Excel can be automated using Power Query, ensuring consistent, repeatable processes.
Data Enrichment: Creates new calculated columns, splits/merges columns, and combines data from multiple sources to create a unified data model.

DAX (DAX Guide)

DAX stands for data analysis expressions, which is the formula language used in Power BI. Just like in excel, we have different formulas used for different types of data.

Text functions are used to manipulate strings. They can also be done during data transformation, as most of the text functions are available when you right-click on the text column that one wishes to modify
Logical functions assess logical expressions, and return information about the values or sets in the expression i.e If, Or, And, Switch
Aggregation functions return values by applying an aggregation function to a column or to an expression e.g Sum and Sumx, Average and Averagex
Date and time functions create calculations based on dates and time. Many of the functions in DAX are similar to the Excel date and time functions e.g DateDiff, Datevalue etc
Filter functions help manipulate tables and filter contexts.
Time Intelligence functions are calculations used to compare and aggregate data over time periods, supporting days, months, quarters, and years e.g Endofmonth, Endofyear

Dashboards

Dashboards are visualizations that help explain data to non-technical users. Different visuals are organized in one view and clearly show the insights that are gotten from data

Features of a good dashboard

Important KPIs should be easy to see
Layout well organized - KPIs at the top and charts within
Should be one page
Should be easy to understand and interactive
Should be able to support easy decision-making
Visuals included should not have generic headings; headings ought to be clear, concise and easily understandable.
Slicers within the dashbard should not be broken; they should be connected to all visuals within the dashboard

# Visualization

When creating a dashboard using the report view, you are required to plot your dashboard on the canvas section of the page labelled A. The various visualizations are available on the highlighted area

One can make various adjustments based on the visualization selected in terms of plotting, formatting and layout on the generated.

Different visualizations:

Line chart - uses lines to visualize trends
Area charts - have the areas filled and show magnitude
Stacked column chart - show both categories' and subcategories' contributions to the total. They have the sum of values of subgroups.
Stacked bar charts - same as the stacked column chart but plotted horizontally
Pie charts - shows proportions of data categories
Donut chart - the same as a pie chart but has a hole in the middle
Treemaps - show hierarchy relationships of the plotted data
CAD Visual – shows aggregated values of 1 entry specifically KPIs. One can also use the Multicad
Bubble map – these are geographical maps with bubbles
Filled map – coloured geographical regions
Scatter charts show correlation between 2 variables. Help identify outliers in data
Funnel charts - show step-by-step flow in data (sequence)
Waterfall charts - visualizes cumulative data & captures outliers
Combo charts - combines the column and bar charts
Bar charts - compare or track changes in data using horizontal bars
Tables - these are used when plotting data as exact values with categories on the rows and summations on the columns
Matrix Tables - these are used when plotting data as exact values with 2 sets of categories, one on the rows, another on the column and summations for each segment
Column charts - compare or track changes in data using vertical bars
Slicers - help bring interactivity within the dashboard based on categories