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

Introduction
Power BI is one of the most powerful tools designed for cleaning data ,creating reports and dashboards.It is preferred over other tools due to its ability to handle large data sets,create real-time dashboards and its beginner friendly.Power BI desktop is a free version provided by Microsoft.
Main uses of Power BI

• Connecting data from different sources:web,excel,SQL.
• Cleaning and transforming data.
• Data modelling.
• Data analysis using DAX.
• Data visualisation:charts,filters,slicers.
• Reports and dashboard creation.
• Data automation, sharing and collaboration using Power BI service.

Data cleaning in Power BI
Data analysts work with complex data sets and cleaning data goes beyond deleting rows and renaming column.Power Bi provides a powerful ETL(Extract, Transform, Load) engine that plays a key role in cleaning messy data to ensure accuracy,consistency and effective analysis.Power BI shows column Quality and distribution which helps analysts identify inconsistencies in their data.Power query is used to clean data in Power BI.

Messy Data
Just like the name suggests messy data has this qualities

• Blank rows and missing values.
• Duplicate records.
• Wrong data types.
• Inconsistent text formats("kiambu","MOMBASA")

Removing duplicates
If your data must have unique values select the columns and remove duplicates.
Correct formatting
Your data should be formatted correctly.

• Text: Employee Name, Country, Product Type.
• Decimal Number: Market Price, Revenue, Gross profit.
• Whole Number or Decimal: Farmer Code, depending on use.
• Date: Planting Date, Harvest Date. Use Transform > Detect Data Type to auto-detect, then adjust manually if needed.

Removing errors and blank values
For numeric columns remove the errors but do not delete the blanks.
For text columns consolidate the null,errors to "Not provided",use consistent labels for the entire data.
Blank values can be replaced with 0 in numeric columns.
Do not delete rows unless it is necessary.

use Replace Values to change "Error" → "Not provided".

Standardising text columns

• Trim to remove extra spaces.
• Uppercase to capitalise each word.

Impacts of a well cleaned data

• Accurate analysis. -Enhanced Power BI performance.
• Proper data modelling. After cleaning your data, Use Transform > Detect Data Type again. Click Close & Apply to load your cleaned data into Power BI.

DAX in Power BI

Sounds like programming,right?
DAX stands for Data Analysis Expressions.
DAX is a formula language used in Power BI to build measures,create calculated columns and calculated tables to transform raw data into meaningful insights.
DAX mainly performs aggregation and iterator functions.
Aggregation Functions
Aggregation is the process of combining rows of data into a single value.
Common Aggregation functions.

• SUM -This adds up all values in a numeric column. Example: Total Revenue Total Revenue = SUM('Kenya Crops'[Revenue (KES)])
• AVERAGE-This adds up the mean of a column. Example: Average Market Price Average Market Price = AVERAGE('Kenya Crops'[Market Price (KES/Kg)])
• MIN/MAX- Calculates the largest and smallest values. Example: Minimum Yield Minimum Yield = MIN('Kenya Crops'[Yield (Kg)]) Example: Highest Profit Maximum Profit = MAX('Kenya Crops'[Profit (KES)])
• MEDIAN Returns the middle value of a column. Example: Median Profit Median Profit = MEDIAN('Kenya Crops'[Profit (KES)])
• COUNT Counts numeric values in a single column. Example: Count Yield values Yield Count = COUNT('Kenya Crops'[Yield (Kg)])

Iterator functions
Performs row by row evaluation and gives a final result.
Examples

• SUMX Evaluates an expression for each row in a table and then sums those results. Total Profit (SUMX) = SUMX( 'Kenya Crops', 'Kenya Crops'[Revenue (KES)] - 'Kenya Crops'[Cost of Production (KES)])
• AVERAGEX Evaluates an expression for each row and then returns the average of those results. Example 2: Average Profit per Acre Average Profit per Acre = AVERAGEX( 'Kenya Crops', DIVIDE( 'Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)])
• COUNTX COUNTX counts rows where an expression returns a non-blank value. Example 1: Count profitable farms Profitable Farms Count = COUNTX( 'Kenya Crops', IF('Kenya Crops'[Profit (KES)] > 0, 1))

Logical Functions in Power BI
They are used to make decisions based on conditions.
Examples IF,Nested IF,OR function, AND function,IF WITH&&.

Example: Profit or Loss Classification (Measure)
Profit Status =IF( SUM('Kenya Crops'[Profit (KES)]) > 0,
"Profitable", "Loss"))

Dashboards in Power BI.

A dashboard is a carefully designed collection of charts that allows businesses to answer specific questions easily and quickly.A dashboard contains a one page high level view of key factors that tell a story at a single glance.Main elements of a dashboard include

• KPI Cards (Revenue, Visits, Profit)

• Trend lines (Sales over years)

• Top / Bottom performers

• Alerts (threshold-based)

• Simple filters and slicers(date, region)
  To create a good dashboard avoid too many visuals ,complex calculations and very large tables.
  Users read dashboards from top to bottom and from left to right. The most important information should be placed at the top and towards the left.
  The top section of a dashboard should contain KPIs. These should be displayed using cards.
  visuals and should be immediately visible.
  The middle section should contain trend and performance visuals.
  The bottom section should contain supporting breakdowns and detailed comparisons.
  Filters should be placed where they are easy to find, at the top or along one side of the dashboard.
  Main Visuals used in a dashboard

• Charts

• Column chart,A column chart displays data using vertical bars to compare values across categories.It is used to compare values across categories.

• Bar chart ,it is similar to a column chart but uses horizontal bars.

• Line chart , line chart shows trends over time using connected data points.Example Gross profit over years.

• Pie charts and Donut chart, these are best for showing percentage contribution of categories to a whole. Examples Revenue share by crop type. Best used when there are few categories (2–5).

• Card visual, it displays a single aggregated value. Use cases: Total revenue and Total profit.

• Matrix visual, it is similar to a pivot table with rows and columns. Examples: Revenue by county and seasons.

• Scatter chart ,Shows relationship between two numeric variables. Examples: Cost vs revenue, Yield vs profit.

• Map (Bubble map), it displays data using geographic locations with bubbles.Example Gross profit by Country. Interactive dashboard

Conclusion.

Effective analysis,accurate and consistent data is a reflection of a well cleaned data.DAX allows data analysts to transform cleaned data and give meaningful insights.A well designed dashboard enables stakeholders to make effective decisions.