Power BI Desktop is a free Windows application where you create reports and
dashboards. You connect your data, clean it, build visualizations, and save your reports.
Power BI Interface.
Ribbon
- Home tab: Get data, Transform data, New Measure, Publish.
- modelling tab: Manage relationships, Insert tab, Add visuals.
- view tab: Control layout, options.
- Format tab: Customize visuals.
- Insert: used to add visual and design elements to your report page
Fields pane
Displays all data fields in your model.
Visualizations Pane
Select and configure charts, tables, visuals.
Views
- Report View: Create and visualize reports.
- Table View: View data in tabular format.
- Model View: Manage table relationships.
Uploading Excel files on Power BI.
Load the Data
Open Power BI desktop then click on get data then Excel workbook. Select the excel file you intend to use then load the file.
Transform Data
Power Query is used to clean, transform, and prepare raw data before analysis.
From the home tab, click on transform data to open power query.
Basic Transformations
Rename Queries
Give your query a meaningful name for easier reference.
In Power Query right-click the default query name (e.g., Sheet1). Click rename then type a meaningful name like KenyaCropsData or HospitalAdmissionsData.
Remove Rows
Helps clean blank or unwanted rows.
Options under remove rows:
- Remove top rows – e.g., remove header rows accidentally repeated.
- Remove bottom rows – to remove totals or notes at the bottom.
- Remove blank rows – for completely empty rows.
- Remove errors – from a column after converting to correct data types.
- Remove duplicates – ensure data uniqueness.
Keep Rows
Opposite of "Remove"; helps retain only what you need.
Options under keep rows
- Keep Top Rows – e.g., top 10 hospitals or counties.
- Keep Bottom Rows
- Keep Range of Rows
- Keep Duplicates – to analyze duplicate entries.
Filter Rows
Exclude invalid entries like “N/A”, “None”, or “Error”.
- Click the filter icon on the column.
- Uncheck values like Error, N/A, None.
- You can also apply Text Filters, Number Filters, or Date Filters for specific conditions.
Remove Blank or Error Rows
- Select the first few rows to check for any completely blank rows or column headers repeated.
- Use "Remove Rows" > "Remove Blank Rows" if any are found.
- Also use "Remove Errors" on numeric columns like "Yield", "Market Price" etc., after converting them to proper types
you can check for blank rows by clicking on the dropdown next to the column name and checking from the list.
Data Types
For each column, set the correct data type:
- Text: Farmer Name, County, Crop Type, Season, Crop Variety, Soil Type, Pest Control, etc.
- Decimal Number: Planted Area, Yield (Kg), Market Price, Revenue, Cost of Production, 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.
Rename Columns
Click on each column header and rename using friendly names
Fix typos like Crop Varie to crop variety, farmer Na to Farmer Name.
Handling Errors.
- Columns like “Yield”, “Crop Variety”, “Fertilizer Used” have errors like "Error" or invalid values.
- For each column click the filter dropdown, deselect "Error", "N/A", or "None" if those aren't valid options.
- Or use Replace Values to change "Error" to null.
Replacing problematic values
Use Transform - replace Values:
- Replace "Error" with "N/A" or null.
- Replace "None" with null (if not meaningful)
- "Short Rain" with Short Rains, "Long Rain" with Long Rains for consistency.
Format Text Data
Standardize formatting of names and categories.
Tools under Transform > Format:
- Uppercase – for consistency (e.g., county names).
- Lowercase
- Capitalize Each Word – best for names like "Crop Type", "County".
- Trim – removes leading/trailing spaces.
- Clean – removes non-printable characters.
Standardize text capitalization.
To capitalize the first letter, whole word in uppercase or lower case, clean or trim.
Use Transform - Format - capitalize each word or select the preferred format.
Remove Duplicates
If each row must be unique (e.g., by Farmer + Crop Type + Season), select those columns then remove duplicates.
Reorder or group columns
- You can move related columns together (e.g., financials: Revenue, Cost, Profit).
- Click and drag columns to reorder
Remove Columns/Rows
- Right click column header then remove
- Filter column (e.g. exclude "Paid" rows)
Split Columns
• Split Name column by space to get First and Last Name
Merge Columns
• Select First and Last Name > Merge > Add separator then OK
Group By
Aggregate data (e.g., total Revenue by County).
- Select the column to group by (e.g., County).
- Go to Transform > Group By.
- Choose aggregation: Sum, Count, Average, etc.
- Add additional groupings if needed.
Sort
Organize your data logically.
- Click column > Home > Sort Ascending or Descending.
- Can sort alphabetically (A-Z), numerically, or by date.
How to Deal with Blanks in Power Query
Identify Blank Cells
Blanks show up as:
- Empty cells (null values).
- Cells with “N/A”, “None”, “Error” — these are not technically blank, but should be treated as such.
How to Find Them:
- Click the filter icon on any column.
- If blanks exist, you'll see (null) as a filter option.
- look for custom errors like "Error", "N/A", "None" especially in text columns.
Replace Blanks with Default or Meaningful Values
Use when the column is important and must not be left empty.
Select the column - go to Transform - Replace Values, Replace null with:
"Unknown" for text, 0 for numbers or "No Data" or "Not Provided" for descriptions.
You can also use Transform > Replace Errors for error-based blanks.
Fill Down or Fill Up
Use when the blank value should be copied from the row above or below.
Example Use Case: If a “County” or “Farmer Name” is listed only once and applies to several
rows.
Select the column.
Go to Transform > Fill > Down or Up.
Remove Rows With Blanks
Use when:
- The blank row has missing critical info (like Revenue, Yield, or Crop Type).
- The row has too many blanks and is not usable.
After cleaning
- Click Close & Apply to load your cleaned data into Power BI.
- Always check the resulting table in the data view for issues.
- Ensure your work is saved.
DAX
DAX (Data Analysis Expressions) is a formula language used in Power BI to create
calculations and data analysis logic. DAX is used to build measures, calculated columns, and
calculated tables that help transform raw data into meaningful insights.
Aggregation
Aggregation is the process of combining multiple rows of data into a single summarized value.
Types of Aggregation Functions in DAX
- Simple aggregation functions (work directly on a column)
- Iterator aggregation functions (end with X and work row by row on an expression)
SUM
SUM adds all numeric values in a column.
SUM works on one numeric column and returns the total based on the current filters.
Total Revenue = SUM('Kenya Crops'[Revenue (KES)])
SUMX
SUMX evaluates an expression for each row in a table and then sums those results.
SUMX is used when the value you want to sum is not stored as a single column but must be calculated row by row first.
Total Revenue (SUMX) = SUMX('Kenya Crops', 'Kenya Crops'[Yield (Kg)] * 'Kenya Crops'[Market Price (KES/Kg)])
AVERAGE
AVERAGE calculates the mean of a numeric column.
Average Yield = AVERAGE('Kenya Crops'[Yield (Kg)])
AVERAGEX
AVERAGEX evaluates an expression for each row and then returns the average of those results.
Average Profit per Acre =
AVERAGEX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))
MEDIAN
MEDIAN returns the middle value in a column when the values are sorted.
Median Yield = MEDIAN('Kenya Crops'[Yield (Kg)])
MEDIANX
MEDIANX evaluates an expression for each row and then returns the middle value of the results
Median Revenue = MEDIANX('Kenya Crops', 'Kenya Crops'[Revenue (KES)])
MIN
MIN returns the smallest value in a column.
Minimum Yield = MIN('Kenya Crops'[Yield (Kg)])
MINX
MINX evaluates an expression for each row and returns the smallest result.
Minimum Profit per Acre = MINX( 'Kenya Crops', DIVIDE( 'Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area(Acres)]))
MAX
MAX returns the largest value in a column.
Maximum Yield = MAX('Kenya Crops'[Yield (Kg)])
MAXX
MAXX evaluates an expression for each row and returns the largest result.
Maximum Profit per Acre = MAXX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))
COUNT counts numeric values in a single column.
COUNTROWS counts the number of rows in a table.
COUNTX counts rows where an expression returns a non-blank value.
ABS (Absolute Value) returns the absolute (positive) value of a number.
POWER raises a number to a given power.
SQRT (Square Root) returns the square root of a number.
MOD returns the remainder after division.
LOGICAL FUNCTIONS IN DAX.
Logical functions in DAX are used to make decisions based on conditions. They allow Power BI
to answer “yes or no” questions, classify data into categories, apply business rules, and control
how results are calculated and displayed.
IF FUNCTION
Evaluates a condition and returns one value if the condition is true and another value if the condition is false.
Profit Status = IF(SUM('Kenya Crops'[Profit (KES)]) > 0, "Profitable", "Loss")
NESTED IF STATEMENTS
A nested IF occurs when one IF function is placed inside another IF. This is used when more than two outcomes are required. Profit Level = IF('Kenya Crops'[Profit (KES)] < 0, "Loss", IF('Kenya Crops'[Profit (KES)] < 50000, "Low Profit", "High Profit"))
This logic first checks for losses. If the farm is not making a loss, it then checks whether profit is
low or high.
IF WITH AND (AND FUNCTION)
The AND function is used when all conditions must be true for a result to be returned.
High Yield & Profitable = IF( AND( 'Kenya Crops'[Yield (Kg)] > 2000, 'Kenya Crops'[Profit (KES)] > 0 ), "Yes", "No")
Here, a farm is marked “Yes” only if it has both high yield and positive profit.
IF WITH && (LOGICAL AND OPERATOR)
The && operator performs the same function as AND, but it is more concise and commonly used in professional DAX code.
High Yield Large Farm = IF('Kenya Crops'[Yield (Kg)] > 2000 &&'Kenya Crops'[Planted Area (Acres)] > 10, "Qualified", "Not Qualified")
IF WITH OR (OR FUNCTION)
The OR function is used when at least one condition must be true.
Risk Category = IF(OR('Kenya Crops'[Profit (KES)] < 0, 'Kenya Crops'[Weather Impact] = "Severe" ), "High Risk", "Normal")
A farm is classified as high risk if it made a loss or experienced severe weather.
IF WITH || (LOGICAL OR OPERATOR)
High Risk Farm = IF('Kenya Crops'[Profit (KES)] < 0 || 'Kenya Crops'[Weather Impact] = "Severe", "High Risk", "Low Risk")
THE SWITCH FUNCTION
The SWITCH function is a cleaner and more readable alternative to nested IF statements. It is
ideal when there are many possible outcomes.
Profit Category = SWITCH(TRUE(), 'Kenya Crops'[Profit (KES)] < 0, "Loss", 'Kenya Crops'[Profit (KES)] < 50000, "Low Profit", 'Kenya Crops'[Profit (KES)] < 200000, "Medium Profit", "High Profit")
NOT FUNCTION
The NOT function reverses a logical condition.
Irrigation Type = IF(NOT('Kenya Crops'[Irrigation Method] = "Irrigated"), "Rain-fed", "Irrigated")
ISBLANK FUNCTION
The ISBLANK function checks whether a value is blank.
Yield Availability = IF(ISBLANK('Kenya Crops'[Yield (Kg)]), "Missing", "Available")
CALCULATE FUNCTION
The CALCULATE function is the most important function in DAX. It evaluates an expression under a modified filter context.
In simple terms, CALCULATE changes “what data is being used” for a calculation.
Maize Revenue = CALCULATE( SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize")
CALCULATE WITH MULTIPLE FILTERS
CALCULATE can accept more than one filter. All filters are combined using AND logic,
meaning all conditions must be true.
Maize Long Rains Revenue = CALCULATE(SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize", 'Kenya Crops'[Season] = "Long Rains")
DAX TEXT FUNCTIONS
They help clean text, create readable labels, combine fields, and standardize values for analysis and
reporting.
CONCATENATE / CONCATENATEX
CONCATENATE joins two text values into one. Used to create readable labels, combine fields, or build descriptive columns.
County Crop = 'Kenya Crops'[County] & " - " & 'Kenya Crops'[Crop Type]
LEFT, RIGHT, MID
- LEFT extracts characters from the start.
- RIGHT extracts characters from the end.
- MID extracts text from the middle.
Extract first 3 letters
Crop Code = LEFT('Kenya Crops'[Crop Type], 3)
Extracts last 4 digits
Farmer Code Short = RIGHT('Kenya Crops'[Farmer Code], 4)
UPPER, LOWER, PROPER
- UPPER → all caps
- LOWER → all lowercase
- PROPER → first letter capitalized
Standardize county names
County Clean = UPPER('Kenya Crops'[County])
Make crop types readable
Crop Type Clean = PROPER('Kenya Crops'[Crop Type])
LEN, TRIM, CLEAN
- LEN counts characters
- TRIM removes extra spaces
- CLEAN removes hidden non-printable characters
Removes extra spaces
County Cleaned = TRIM('Kenya Crops'[County])
Checks text length
County Length = LEN('Kenya Crops'[County])
DATE AND TIME FUNCTIONS
Date and time functions are used to work with dates, extract parts of dates, and perform date
based calculations.
DATE, YEAR, MONTH, DAY
- DATE creates a date
- YEAR extracts the year
- MONTH extracts the month
- DAY extracts the day
Extracts tear from planting date
Planting Year = YEAR('Kenya Crops'[Planting Date])
TODAY and NOW
- TODAY returns today’s date
- NOW returns current date and time
TIME INTELLIGENCE FUNCTIONS
Time intelligence functions allow you to compare performance across time periods.
DATEADD
DATEADD shifts the current date context backward or forward.
Revenue Last Year = CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, YEAR))
DATEDIFF
Calculates the difference between two dates
Growth Days = DATEDIFF( 'Kenya Crops'[Planting Date], 'Kenya Crops'[Harvest Date], DAY )
SAMEPERIODLASTYEAR
Returns the same period as the current one, but last year.
Revenue last year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
TOTALYTD, TOTALMTD, TOTALQTD
- Year-to-date
- Month-to-date
- Quarter-to-date
Year-to-date revenue
Revenue YTD = TOTALYTD([Total Revenue], 'Date'[Date])
Month-to-date profit
Profit MTD = TOTALMTD( [Total Profit], 'Date'[Date])
Dashboards
A dashboard is a single-page, interactive summary view of your most important data and KPIs.
Dashboards helps you monitor performance, track metrics, make quick decisions and share insights easily.
From Report view on visualizations select the presentation you would like to use. i.e. column charts, pie charts, slicers, bar charts, tables, etc.
From data select the data you want on the x and y axis. i.e. county, Total revenue, Harvest date.
- Add KPIs using the card visualization and slicers using the slicer.
- Create a dashboard by copying visualizations from one sheet to the Dashboard sheet.
- Ensure your dashboard is not redundant. The visualizations should show different comparisons and should have a theme.
Top comments (0)