Before you touch the data

Write the question as a decision: "Should we extend evening delivery slots in the two busiest cities?" beats "look at delivery data". Then write what answer would change the decision, where the data lives, and when you will stop (Asking questions).

• Who will act on the answer, and by when?
• Which number, at what grain: per order, per store per day, per customer per month?
• What is the comparison: last year, another city, a target?
• What would make you distrust the data: missing days, test orders, refunds?

Spreadsheet formulas: "I want to..."

Formulas marked 365 need Microsoft 365 or Excel 2021 and later; Google Sheets supports most of them under the same names. In Sheets, separators may be ; instead of , depending on your locale. Examples use a Mangrove sales table with columns Store, Date, SKU and Amount (Data cleaning, Pivot tables).

Spreadsheet habits that save hours

• Turn every data range into a table (Ctrl+T in Excel). Formulas then read Sales[Amount], and new rows join charts and pivots on their own.
• Keep raw data on one sheet, untouched. Do cleaning in new columns or in Power Query, so you can redo it when next month's file arrives.
• Lock a reference with $: $B$1 fixed, B$1 row fixed. Press F4 in Excel for Windows to cycle through them while editing.
• One fact per cell, one record per row, one header row. No merged cells in data.
• Paste as values before you send a file someone will sort, so formulas do not break.
• Store IDs and SKUs as text so leading zeros survive; format the column as Text before you paste.

Pivot tables

A pivot table is a GROUP BY you build with the mouse. Start from a clean table with one row per event (Pivot tables).

Cleaning checklist

Run these on every new file before you trust a total (Data cleaning).

Power Query in one breath

In Excel, Data, Get Data (Get & Transform, Excel 2016 and later) loads a file or folder into Power Query, where every cleaning step you click is recorded: remove columns, change type, split column, trim, fill down, unpivot, merge queries (a join), append queries (stack files). Next month, drop the new file in the folder and press Refresh; every step runs again. Power BI uses the same editor (Data cleaning, Warehouse models).

pandas: "I want to..."

Written for pandas 2.x. Import with import pandas as pd. The spreadsheet column shows the same move in Excel (Python and pandas).

pandas traps

• Chained assignment such as df[df.x > 0]["y"] = 1 may change a copy and not df. Use one df.loc[df.x > 0, "y"] = 1.
• groupby drops rows whose key is NaN unless you pass dropna=False.
• A merge on a key that repeats on both sides multiplies rows. validate="m:1" raises an error instead of giving you a wrong total.
• mean() skips NaN, so an average over a column with gaps describes only the rows that have a value.
• IDs read as numbers lose leading zeros. Pass dtype for them when reading.

Which chart

Pick the chart from the question, then remove everything that does not help answer it. Title the chart with the finding, not the metric name (Charts, Dashboard design).

Dashboard rules

A dashboard answers a few recurring questions for a named audience (Dashboard design, Agreed metrics).

• Top left holds the most important number. Read order: headline numbers, then trends, then breakdowns, then detail.
• Every number has a comparison and a definition one click away: what counts, which time zone, refreshed when.
• Five to nine tiles per page. More means two audiences; split it.
• Use one metric definition across the company (a metric layer or certified dataset), so the store team and finance see the same "net sales".
• Show when the data last refreshed, and alert the owner when a load fails (Data reliability).
• Start every axis for bars at zero. Lines may zoom, but say so.
• Colour means something: one highlight colour for the thing to look at, grey for the rest.

Power BI measures (DAX)

A measure is computed at query time for whatever filters the visual has; a calculated column is computed once per row. Prefer measures for totals and ratios (Warehouse models, Agreed metrics).

Net Sales = SUM ( Sales[AmountCents] ) / 100
Orders = DISTINCTCOUNT ( Sales[OrderId] )
Avg Basket = DIVIDE ( [Net Sales], [Orders] )
Sales S-014 = CALCULATE ( [Net Sales], Site[SiteId] = "S-014" )
Sales LY = CALCULATE ( [Net Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YTD = TOTALYTD ( [Net Sales], 'Date'[Date] )
Growth % = DIVIDE ( [Net Sales] - [Sales LY], [Sales LY] )

DIVIDE returns blank instead of an error on zero. Time intelligence needs a separate date table marked as a date table, joined to the fact table.

Star schema in one breath

Fact tables hold events with numbers (one row per sale line, per order, per delivery). Dimension tables describe them (store, article, date, customer). Facts join to dimensions by key, and dimensions never join to each other through the fact. Decide the grain of each fact table first and write it down: "one row per order line" (Reporting model, Warehouse models).

Using AI for analysis

AI assistants are fast at writing formulas, pandas code and first-draft summaries, and they make confident mistakes (AI analysis).

• Give the column names, types and three sample rows; never paste customer personal data into a tool your company has not approved.
• Ask for code, then run it yourself, so every number comes from the data and not from the model.
• Check one result by hand: pick one store and one day and recompute it.
• Ask the model to list its assumptions about the data, then check each one.

Telling the story

Lead with the answer, then the evidence, then what you recommend and what you do not know. One page, one chart per point (Data story).

Answer:   Evening "Now" orders in the North and East cities grew 31% since July;
          one extra rider shift from 6 to 9 pm would cut late orders by about half.
Evidence: chart of late orders by hour; table of riders on shift vs orders.
Ask:      approve a 4-week trial in 20 stores, review on 30 November.
Caveats:  July had two festival weekends; refunds are not yet in the data.

Glossary

Plain definitions, with the lesson that explains each one.

Aggregation

Turning many rows into one number, such as a sum, count or average. Pivot tables

Calculated column

A column computed once per row and stored in the model. Warehouse models

Data model

Tables and the relationships between them that a pivot or BI tool reads. Reporting model

Dimension

A table or field you slice by: store, date, article, city. Reporting model

Dynamic array

An Excel formula result that spills into several cells, such as UNIQUE or FILTER. Data cleaning

ETL and ELT

Extract, transform, load: cleaning data before loading it, or loading raw data first and transforming it in the warehouse. Warehouse models

Fact table

A table of events with numbers, at one declared grain. Warehouse models

Grain

What one row means, such as one order line or one store per day. Reporting model

KPI

Key performance indicator: one of the few numbers a team agrees to steer by. Agreed metrics

Measure

A calculation evaluated for whatever filters are in place, such as net sales. Agreed metrics

Metric definition

A written rule for a number: what counts, what is excluded, which time zone, who owns it. Agreed metrics

Outlier

A value far from the rest, which is either an error to fix or a story to tell. Data cleaning

Pivot table

An interactive summary table that groups rows and totals values. Pivot tables

Power Query

Excel's and Power BI's recorded, repeatable cleaning steps. Data cleaning

Self-service BI

Letting business users build their own reports on top of certified, well-named datasets. Self-service

Slicer

A clickable filter on a pivot table or dashboard. Pivot tables

Star schema

Fact tables in the middle, joined to dimension tables around them. Warehouse models

Tidy data

One variable per column, one observation per row, one value per cell. Python and pandas

Unpivot (melt)

Turning month columns into rows of month and value, so tools can group them. Python and pandas

Year over year (YoY)

Comparing a period with the same period last year, which removes seasonality. Charts

Courses on Kolom

• Data Analysis with Excel, Python & AI: questions, cleaning, pivots, charts, pandas, AI help and the final story.
• Dashboards and Business Intelligence: reporting models, agreed metrics, dashboard design and data reliability.
• SQL & Data Modeling: pull your own data instead of waiting for an export.
• Statistics for Data Work: know when a difference is real.
• Metrics & Product Analytics: funnels, retention and experiments for product questions.

What to read next

• Storytelling with Data by Cole Nussbaumer Knaflic. Choosing and decluttering charts for a business audience.
• Python for Data Analysis by Wes McKinney, the creator of pandas, free online at wesmckinney.com/book.
• The pandas user guide at pandas.pydata.org. The "10 minutes to pandas" page is the fastest start.
• Microsoft's Excel function reference at support.microsoft.com/excel, which notes which version each function needs.
• The Data Warehouse Toolkit by Ralph Kimball and Margy Ross. The classic on star schemas and dimensional modeling.