Data & Analytics
Data Analysis with Excel + Power BI
Clean data in Excel, then model and show it in Power BI.
Data Analysis with Excel + Power BI is the path from a raw export to a report someone can filter. You will clean data in Excel, then load it into Power BI for a model and a few visuals. The course stays with business questions, not chart decoration.
Course syllabus
01. A sheet that is one table
- What a clean table looks like
Rows, columns, formats, and why a messy sheet breaks later work.
- One header row
The column names sit in row 1, and the rows under them are the facts.
- One value per cell
A cell holds a date, a name, or an amount, not a note that mixes all three.
- No merged cells in the data
A merge hides which row a value belongs to when you load the range.
- The same kind of value down the column
Dates stay dates and amounts stay numbers, from the first fact to the last.
- A column name that includes the unit
Amount GBP tells you the currency; Value does not.
- Blank rows are not part of the table
An empty row splits the range and will confuse the load.
- The sheet contains only this table
Comments and a second grid belong on another sheet.
02. Clean values before you analyse
- Cleaning before analysis
Fix types, blanks, and duplicates in Excel before Power BI sees the file.
- Find cells that should have a value
A blank amount is not zero until you decide that it is, and write the zero.
- Dates stored as text
A date that will not sort is text, so convert it before you load the sheet.
- Numbers with a currency sign in the cell
Strip the symbol so the column is numeric, and keep the currency in the header.
- Duplicate rows
Two identical orders will double a total, so find them and keep one.
- Extra spaces in names
North with a trailing space and North will chart as two categories.
- Fill a missing category from a lookup
Match a code to a name with a formula you can check on a few rows.
- Name the cleaned range
A named table is the range you will load, not the whole worksheet.
03. The question the report must answer
- The business question in one line
Write the sentence the report must answer before you open Power BI.
- One row means one thing
Say whether a row is an order line, a day, or a payment.
- A measure you can say in words
Total sales, or orders per customer, stated before any formula.
- A date column you can filter
Month and year come from a real date, not from a label typed in a cell.
- The categories you will group by
Region, product, or channel: the columns that already exist on the sheet.
- Charts you will not build
A visual with no question behind it stays off the page.
- A sample of ten rows
Read them, and if a row is nonsense, fix the sheet before the load.
- Put the question on the brief
Question, grain, measure, and the file path, on one page.
04. Load the table into Power BI
- Loading the data
Bring the named table in, with headers and column types set on purpose.
- Get data from the Excel file
Point Power Query at the workbook and pick the named table, not a spare sheet.
- Use the first row as headers
Column names become fields, and they must not remain as a data row.
- Set each column's type
Date, whole number, decimal, or text, chosen by you rather than left to a guess.
- Drop columns you will not use
Fewer fields leave a model you can still explain.
- Filter out rows that are not facts
A totals row at the bottom is not another order.
- Close and apply
The steps run, and the table lands in the model.
- Check the row count
It should match the cleaned table, minus any rows you filtered out.
05. Relationships and measures
- A simple model and measures
A date table, one relationship, and measures that answer the question.
- A date table for the calendar
One row per day, so a slicer can still offer months that have no sales.
- Relate the fact table on the date
Draw the relationship from the date table to the orders, on the date column.
- Filter in one direction
The date table filters the orders, and the orders do not filter the calendar.
- A measure is not a calculated column
The total is computed for the current filter, not stored on every row.
- Sum of the amount
A measure that adds the sales column and nothing else.
- A measure that divides two sums
Average order value is total sales divided by the count of orders.
- Put the measure on a card
If the card is wrong, fix the measure before you build any chart.
06. Charts that answer the question
- Visuals with a point
Each chart answers part of the question, and decoration does not get a place.
- Bars for the category totals
One bar per region or product, using the measure you already checked.
- A line for the amount over time
Months on the axis, and the measure as the line.
- A slicer that filters every visual
Month or region, chosen so both charts move together.
- A table when someone needs the figures
The same measure in rows, when a chart would hide the number.
- Colour for one category you must see
One colour marks the region in the question, and the rest stay neutral.
- A title that states the result
North led sales in March is a finding; Sales by region is only a label.
- Delete a visual that does not answer
If you cannot say what the chart is for, take it off the page.
07. Filters and the total they change
- What a filter does to a measure
The measure recalculates for the rows the slicer still includes.
- A slicer changes the card
Pick a month and watch the total move, which is the check that context works.
- The grand total and the bars
The total can include rows the chart has grouped, so know which figure you are reading.
- A measure that ignores one slicer
Use it when a comparison must stay put while the rest of the page filters.
- A page filter for the year
Every visual on the page stays inside that year.
- A filter on one visual only
One chart shows a single product, while the rest of the page stays broad.
- Check the figure against Excel
Apply the same filter and the same sum in the sheet, and compare.
- When the numbers disagree
Look at filters, the relationship, and a totals row you may have loaded.
08. A one-page report
- One page for one question
Lay the brief out so a colleague can read the answer without a tour.
- The figures a manager can read
Axis labels and units sit on the chart, so it does not need a spoken explanation.
- Sort the bars by the measure
Largest first, so the answer is at the top of the chart.
- Labels that fit on the screen
Use short names on the axis, and leave the long name for a tooltip.
- A note on what the data leaves out
Say if returns, a missing month, or a region never entered the file.
- Refresh after the sheet changes
Run the query again instead of pasting a new export by hand.
- Keep the file path you can find
Store the workbook and the report where the next person can open both.
- Add a visual only when someone asks
A second question gets its own page, after you write that question down.
Lecture videos stay with the course. Playback for enrolled students will open once payments are available. Video links are not published on this page.