Power Bi: Turning Messy Data Into Actionable Insights

Introduction

Within organizations, data analysts have the crucial task of deriving meaning from various data sets. In its true form, data does not provide substantial insights that can be used to drive meaningful change within organizations. Here is where data analysts come in to clean and analyze the data and to visualize their findings. To carry out these tasks, analysts employ various analytics tools. One of the widely used tools in the industry is Power BI. This article aims to demonstrate how data analysts and data professionals at large use Power BI to translate messy data into action, while touching on concepts such as data transformation with Power Query, analysis with DAX, and creating visuals and dashboards.

Power BI Overview

Power BI is a business analytics and intelligence tool, which is part of Microsoft's Power Platform, used to analyze data and create interactive visual reports and dashboards. Power BI can be downloaded directly from the Microsoft Store.
Click the following link to download: Download Power BI.
The Power BI window contains the following components:

Menu Bar

Located at the top of the window and it is organized into tabs such as Home, Insert, Modeling, View, Optimize, and Help.

View Selector / navigation pane

Found on the left hand side and it contains the reports view, table view, model view, and the Dax query and TMDL view.

Visualizations Pane

Lets you choose different visual types (bar chart, line chart, map, table, etc.) and includes options to format visuals and manage visual-level filters

Data Pane

Displays tables, columns, and measures from the loaded dataset

Filters Pane

Used to apply filters at different levels(Visual-level filters, Page-level filters, Report-level filters)

In addition to its intuitive navigation, one of the beauties of Power BI is that it allows analysts to load and work with files in various formats, for example: CSV, text, Excel, Dataflow, Dataverse, etc. To see all these formats, go to the menu bar >Home>Data>Get data.

Transforming Data Using Power Query

Data obtained in an organization often contains inconsistencies, missing values, duplicates, or errors. Data transformation refers to the process of cleaning, reshaping, and preparing this data so that it is accurate, consistent, and suitable for analysis and reporting. Power BI provides access to another Power Platform tool referred to as Power Query, whose sole purpose is to prepare and transform data before analysis. To access Power Query within Power BI go to menu bar>Home>Queries>Transform Data. This will open up a separate window for the Power Query editor.

By simply selecting the rows and columns you want to modify and navigating to the editor's menu bar to find the action(s) you want to perform(for example: remove, split, merge, change data type, etc.), you can make all the necessary and desired transformations for your data. Once done with the transformations, you can click the Close>click and apply changes button to ensure the changes in Power Query are updated in Power BI.

Data Analysis

After satisfactorily transforming or cleaning the dataset, the next step is analysis. It involves examining data to discover patterns, trends, and insights that will ultimately support decision-making. It involves the implementation of statistical techniques, logical reasoning, and analytical tools to convert raw data into meaningful information. It is through analysis that analysts are able to extract meaning from data. In Power BI, analysis is done mainly by using the Data Analysis Expressions (DAX) tool.

Data Analysis Expressions (DAX)

DAX is a formula language for Power BI to perform calculations on data. There are 5 main DAX function categories:
Aggregate: Summarize data, similar (Sum, Average, AverageX, SumX)
Logical: Test conditions and return TRUE/FALSE
Calculate and Filter: Calculate dynamically based on filters or context.
Text: Manipulate text columns.
Date and Time: Handles dates and periods
In Power BI, DAX expressions are written in the formula bar, which is just below the menu bar.

Once these calculations are carried out, they can either be recorded as a new measure or a new column. Therefore, before carrying out any DAX function, select whether the new values should be recorded as a measure or column, depending on the function you want to use. This can be done by going to menu bar>calculations>new measure/new column.

Aggregate functions

The most common include:
SUM: Adds all the numbers in a column.

sum of column = sum(table_name[column_name](unit_of_measurement)) 

AVERAGE: Returns the average of a column

average of column = average(table_name[column_name](unit_of_measurement)) 

SUMX: Adds up an expression for each row in a table.

sum of expression = sumx(table_name, expression) 

AVERAGEX: Calculates an average based on an expression per row.

average of expression = averagex(table_name, expression) 

MAX: Retrieves the largest value in a column

max of column = max(table_name[column_name](unit_of_measurement)) 

MIN: Retrieves the smallest value in a column

min of column = sum(table_name[column_name](unit_of_measurement)) 

Expressions are calculations performed between 2 or more columns of a table.

Logical functions

The most common include:
IF: Returns one value if a condition is true, and another if false.

Result = IF(condition, "value if true", "value if false") 

AND: Returns TRUE if all conditions are true.

Result = IF(AND(comma separated conditions) "value if true", "value if false") 

OR: Returns TRUE if at least one condition is true.

Result = IF(OR(comma separated conditions) "value if true", "value if false") 

NOT: Reverses a logical value.

Result = IF(NOT(condition) "value if true", "value if false") 

To use more than 2 conditions in AND and OR functions, use the IF function together with && (ampersand operator) and || (double pipe) respectively.

A nested if statement (multiple if statements meshed together) can be provides more than 2 possible results (multiple results). However, it can only be used to evaluate only one column.

Calculate and Filter functions

calculate : changes the filter context of a calculation and then evaluates an expression under those new filters. Only done for one column at a time.

result = Calculate(Expression, condition) 

Filter : Returns a table that contains only rows that meet a condition.

new table = Filter(source_table, condition) 

Text functions

The common function include:
CONCATENATE : Joins two text values together.
CONCATENATEX : Concatenates text from a column or table with a delimiter.
LEN : Counts the number of characters in text.
UPPER and LOWER : Converts text to uppercase and lowercase respectively
PROPER : Capitalizes the first letter of each word.
TRIM : Removes extra spaces from text (except single spaces between words).

Data and Time functions

The most common functions include:
TODAY : Returns current date
NOW : Returns current date and time
DATE : Creates a date
DAY : Extracts day
MONTH : Extracts month number
DATEDIFF : Difference between dates

DATEDIFF(lower_date, higher_date, DAY) 

DATEADD : Shift date context by either a month, day, or year.

shifted_date = DATEADD(date_column, operation, DAY/MONTH/YEAR) 

operation refers to the number of days/months/years you want to shift the date (e.g., +1, +3, -4, -5)

Data Visualization

Visualization involves the graphical representation of data using visual elements such as charts, graphs, tables, maps, and dashboards to communicate information clearly and effectively. After generating the measures using DAX, it is possible to visualize your calculations thanks to the reports view tool in the view selector.

To visualize a measure, you first select the visual on the visualization pane you want to use, then locate the measure you want to represent on the data pane. You can apply some filters from the filters pane, depending on how you want your visual to appear and how to represent your data.

Power BI supports various visuals to represent data and they can all be located on the visualization pane. Some of the most popular visuals include:

1. Card - Displays a single key value, such as total sales or profit.
2. Bar Chart - Uses horizontal bars to compare values across categories.
3. Column Chart - Uses vertical bars to show data changes across categories or time.
4. Line Chart-Shows trends over time using connected data points.
5. Pie Chart - Displays proportions of a whole.
6. Donut Chart - A variation of the pie chart with a hollow center.
7. Map - Visualizes data based on geographic location.
8. Treemap-Displays hierarchical data using nested rectangles.
9. Scatter Chart - Plots values using two numeric axes to show relationships.
10. Slicer-Acts as an interactive filter for reports.

Bars, lines, and scatter plots within the accompanying visuals can also serve as interactive slicers.

The Dashboard

A dashboard is a single-page visual display that presents key information needed to monitor performance and support quick decision-making. It is formed by bringing together and arranging key visuals that are deemed crucial (depending on the goals of the analysis) on a single view.

Conclusion

Regardless of the field and type of data, data analysts are capable of deriving meaning from the data presented by following several steps with the help of various tools. Power BI is one of the most sought-after tools by data analysts, given its intuitive interface, support for various data types, and its vast array of technical, analytical, and visualization capabilities. From carrying out transformations using Power Query to using DAX for calculations and analysis, and creating visualizations from the visualization pane, Power BI is definitely a powerful tool for any data analyst or professional seeking to derive meaning from their data.