How to fix inconsistent casing in excel data before importing to power bi
- Step 1Identify the dimension columns that drive slicers — Region, Category, Product, Status — the columns used as slicers, axes, or group-by keys are the ones whose casing must be consistent.
- Step 2Export the source to CSV — Excel: File → Save As → CSV UTF-8. The Case Normalizer redirects to the CSV Case Converter, which reads CSV — clean the data before Power Query, not inside it.
- Step 3Normalise each dimension to its convention — Title for category/region names, UPPER for codes (
EMEA,GB). Tick only the dimension columns so measures/IDs are untouched. - Step 4Convert and confirm the count — Run it; the cells-changed count tells you how many variants were re-cased before they could fragment the model.
- Step 5Trim whitespace if a slicer still duplicates — A trailing space (
LondonvsLondon) survives a case fix and still splits the slicer. Run csv-whitespace-trimmer on the same column to finish. - Step 6Load into Power BI — Get Data → Text/CSV → point at the cleaned file → Load. Slicers, axes, and
DISTINCTCOUNTnow show one value per category.
Source-side vs Power Query case handling
Doing it once at the source beats repeating it in every refresh.
| Approach | Where it runs | Cost per refresh | Notes |
|---|---|---|---|
| This tool (pre-import) | Browser, once on the source CSV | None — baked into the file | Clean data lands in the model already normalised |
Text.Upper / Text.Proper step | Power Query, every refresh | Re-applied each refresh | Fine, but must be re-added if the column is replaced |
DAX UPPER() in a measure | At query time | Every visual interaction | Doesn't fix the column itself — slicer still shows variants |
Mode per dimension type
Match case to the dimension so slicer labels read naturally.
| Dimension | Mode | Example |
|---|---|---|
| Region / Category name | Title | emea region → Emea Region |
| Region / BU code | UPPER | emea → EMEA |
| Product name | Title | widget pro → Widget Pro |
| Email (if a dimension) | lower | Sue@X.com → sue@x.com |
| Status flag | UPPER or Title | active → Active / ACTIVE |
Tier limits (the CSV Case Converter you reach)
CSV-family limits apply because the Excel page redirects to the CSV tool.
| Tier | Max file size | Max rows | Files at once |
|---|---|---|---|
| Free | 2 MB | 500 | 2 |
| Pro | 100 MB | 100,000 | 10 |
| Pro-media | 500 MB | 500,000 | 50 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Dimension-column before/after, the Power Query alternative side by side, and the trailing-space slicer trap.
Region slicer: three values become one
Title-case the Region column in the source so the Power BI slicer shows one Emea instead of emea, EMEA, Emea.
Source (Region): emea EMEA Emea Mode: title, Region only -> all become 'Emea' Power BI slicer (after load): one value, correct DISTINCTCOUNT.
Codes to UPPER for a clean axis
Business-unit/country codes belong uppercase; this gives one axis label per code.
Source (Country): gb GB Gb Mode: upper, Country only Power BI: single 'GB' on the axis.
Pre-import vs Power Query Text.Proper
You can capitalise in Power Query, but it re-runs every refresh and must be re-added if the column is replaced. Normalising the source once is simpler.
Power Query step (every refresh):
Table.TransformColumns(Source, {{"Region", Text.Proper}})
This tool (once, baked into the CSV):
Mode: title, Region only -> done; no applied step needed.Slicer still duplicates after the case fix — trailing space
Case is fixed but London and London still show as two slicer values. The residual cause is whitespace, not case.
After case pass (Region): London London <- trailing space Slicer shows TWO London entries. Fix: csv-whitespace-trimmer on Region -> one entry.
Scope to dimensions, leave measures alone
Only re-case the slicer/axis columns. Numeric measure columns and surrogate keys must stay byte-identical.
Tick: Region, Category, Product. Leave unticked: SalesAmount, OrderID, DateKey. Mode: title. Only the dimension data cells change.
Edge cases and what actually happens
Page redirects to the CSV Case Converter
By designThe Excel Case Normalizer redirects to the CSV Case Converter. Save your dataset as CSV UTF-8, normalise the dimension columns, then load the CSV into Power BI via Get Data → Text/CSV.
Slicer still shows duplicates after the case fix
ExpectedA trailing/leading space or CHAR(160) non-breaking space survives a case-only fix and still splits the slicer. Run csv-whitespace-trimmer on the same column to collapse them.
Fix the source, not a DAX measure
ExpectedUPPER() in a DAX measure changes a calculated result, not the dimension column — the slicer still lists every casing. Normalise the source column (or a Power Query step) instead.
Synonyms aren't case variants
LimitationEMEA vs Europe are different values, not different cases — normalisation won't merge them. Use a mapping table in Power Query or excel-fuzzy-dedup on the source.
Header row preserved for Power Query mapping
PreservedColumn headers aren't converted, so Power Query's 'Promoted Headers' step and column references keep working after you reload the cleaned CSV.
Hyphenated category under Title
Expectedpay-as-you-go → Pay-as-you-go (hyphen not a token boundary). Split on the hyphen first if you need each segment capitalised in the slicer.
Don't re-case surrogate keys
ExpectedIf keys are alphanumeric (AB-100), converting them changes the join value. Scope the pass to display dimensions only and leave key columns out of the selection.
Free tier 500-row cap on a fact-table export
Tier limitThe CSV Case Converter caps Free at 500 rows. A Power BI source of more than that needs Pro (100,000 rows) or higher — or normalise the dimension as a separate small lookup table.
Sentence case is risky for dimensions
ExpectedSentence mode only fixes the first character, so mid-string case differences can still split a slicer. Prefer Title or UPPER for dimension columns.
Accented labels need UTF-8 on both ends
SupportedSão Paulo, Zürich convert fine; save and re-open as CSV UTF-8 and load with the UTF-8 encoding in Power BI's Text/CSV connector so labels don't garble.
Frequently asked questions
Why does my Power BI slicer show London, LONDON, and london?
The dimension column reached the model with mixed case from a messy Excel source. Normalise the column to one case in the source before import and the slicer collapses to a single London.
Should I fix case in Power Query or before import?
Before import is simpler — it bakes clean values into the file once. A Power Query Text.Proper/Text.Upper step works too but re-runs every refresh and must be re-added if the column is replaced.
How do I run it on an Excel file?
The Excel Case Normalizer redirects to the CSV Case Converter. Save the dataset as CSV UTF-8, normalise the dimension columns, then load the CSV into Power BI.
Which case should dimensions use?
Title for category/region/product names, UPPER for codes and business units, lower for emails. Pick one per column so every variant becomes identical in the model.
My slicer still duplicates after normalising — why?
Almost always a trailing space or non-breaking space. Run csv-whitespace-trimmer on the same column; case + trim fixes nearly every duplicate slicer value.
Will a DAX UPPER() measure fix the slicer?
No — a measure produces a calculated value; it doesn't change the dimension column the slicer reads. Fix the column at the source or in Power Query.
Does it merge EMEA and Europe?
No — those are different values, not different cases. Use a mapping table in Power Query or excel-fuzzy-dedup for synonyms.
Will it change my column headers?
No — only data rows. Power Query's promoted-headers step and column references keep working after you reload the cleaned CSV.
Can I scope it to dimension columns only?
Yes — tick only the slicer/axis columns. Measures, surrogate keys, and date keys stay byte-identical.
Is my dataset uploaded?
No. The CSV Case Converter runs in your browser; the dataset stays local until you load it into Power BI yourself.
How big a source can I prep?
Free 500 rows / 2 MB; Pro 100,000 / 100 MB; Pro-media 500,000 / 500 MB; Developer unlimited. For very large fact tables, normalise the dimension as a smaller lookup table instead.
Should I use Sentence case for a dimension?
Usually not — it only fixes the first character, so mixed-case mid-string values can still split a slicer. Title or UPPER are safer for dimensions.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.