How to standardize measurement units in excel before loading into power bi
- Step 1Identify which measurement columns are unit-inconsistent — Before building the model, check the source export for columns that carry more than one unit — most often a metric/imperial split across regional files, or a column whose meaning changed when a data source was swapped. Document the source unit for each column.
- Step 2Open the converter (Pro tier) — Unit Converter requires Pro or higher; Free can't run it. Pro handles 50 MB / 100,000 rows — adequate for most operational extracts feeding a Power BI dataset.
- Step 3Upload the pre-import export — Accepts
.xlsx,.xls,.ods, and.csv. Only the first sheet is read, so point it at the sheet Power Query will consume. Parsing is browser-local. - Step 4Add a rule per column to reach the target unit — For each column, set the exact header, × Multiply or ÷ Divide, and the factor that brings it to your chosen base unit (e.g. everything to kg: lbs column ÷ Divide 2.20462).
- Step 5Keep raw values if your report needs them — Name a New col name to preserve the original reading for a drill-through or audit visual. Otherwise leave it blank to overwrite in place and keep the column count stable for an existing Power Query schema.
- Step 6Download and point Power Query at the result — Download
units-converted.xlsx. Load it (or replace the source) in Power Query — the column is now single-unit, so your DAX measures aggregate correctly without any unit-aware workaround.
Unit standardisation before vs after Power BI
Where to fix unit inconsistency, and what each approach requires. Pre-converting in the source is the only option when rows carry no unit flag.
| Approach | When it works | Requirement |
|---|---|---|
| Pre-convert in Excel (this tool) | Always — fixes the value at source | Know each column's source unit |
| Power Query custom column | If a per-row unit flag exists | M expression branching on the flag column |
| DAX calculated column | If a per-row unit flag exists in the model | Loads both units, then converts (heavier model) |
| DAX measure-level conversion | Rarely clean — measures shouldn't carry unit logic | Complex, error-prone, hard to audit |
Common 'normalise to base unit' rules
Pick one base unit per measure, then write a rule that brings the off-unit column to it. Reverse direction by swapping Multiply and Divide.
| Source → base unit | Operation | Factor |
|---|---|---|
| lbs → kg | ÷ Divide | 2.20462 |
| miles → km | × Multiply | 1.60934 |
| US gallons → L | ÷ Divide | 0.264172 |
| inches → cm | × Multiply | 2.54 |
| cents → dollars | ÷ Divide | 100 |
| g → kg | ÷ Divide | 1000 |
Tier limits for production exports
Pro-gated tool. Free cannot run it. One file per run.
| Tier | Max file size | Max rows | Runs this tool? |
|---|---|---|---|
| Free | 5 MB | 10,000 | No — Pro required |
| Pro | 50 MB | 100,000 | Yes |
| Pro-media | 200 MB | 500,000 | Yes |
| Developer | 500 MB | Unlimited | Yes |
Cookbook
Rule setups for the unit problems that quietly corrupt Power BI aggregations — and the one case (per-row unit flags) the converter can't solve alone.
Normalise an imperial regional export to kg
Two regional files feed one dataset: EU in kg, US in lbs. Convert the US file's weight column to kg so the appended fact table is single-unit before Power Query merges them.
US file rule: Column = Weight_lbs, ÷ Divide, Factor = 2.20462, New col = Weight_kg Input: Weight_lbs = 22.0462 Output: Weight_kg = 10 Now both regional files share Weight_kg; SUM(Weight_kg) is valid.
Cents → dollars before a currency measure
A finance extract stores amounts in cents. Divide by 100 so DAX SUM gives dollars, not cents, in every card and matrix.
Rule: Column = Amount_cents, ÷ Divide, Factor = 100, New col = Amount_usd Input: Amount_cents = 199900 Output: Amount_usd = 1999 SUM(Amount_usd) now reads in dollars across the report.
Several measures standardised in one pass
Bring weight, distance, and volume to base units together, so the whole fact table is consistent before a single load.
Rule 1: Weight_lbs ÷ Divide 2.20462 → Weight_kg Rule 2: Dist_mi × Multiply 1.60934 → Dist_km Rule 3: Vol_gal ÷ Divide 0.264172 → Vol_L One units-converted.xlsx, three single-unit measure columns.
Per-row unit flag — split first
If a single column mixes units row by row, identified by a 'Unit' flag column, the converter can't branch per row. Split by the flag, convert one part, then re-append.
Source (one column, mixed units): Value=10, Unit='kg' Value=22, Unit='lb' Step 1: split rows by the Unit column with /excel-tools/excel-conditional-splitter Step 2: on the 'lb' subset, Value ÷ Divide 2.20462 Step 3: re-append; now every Value is kg.
Keep the raw value for drill-through
Name a new column so the report can show both the standardised value (for measures) and the original reading (for detail pages).
Rule: Distance_mi × Multiply 1.60934 → Distance_km Input: Distance_mi = 50 Output: Distance_mi = 50, Distance_km = 80.467 Use Distance_km in measures; expose Distance_mi on drill-through.
Edge cases and what actually happens
Single column with a per-row unit flag
Not handled directlyThe converter applies a fixed factor to a whole column — it does not read a per-row 'Unit' flag and convert conditionally. Split the rows by the flag first with the Conditional Splitter (/excel-tools/excel-conditional-splitter), convert the off-unit subset, then re-append. Only then is the column safely single-unit for DAX.
Output is values-only — formulas dropped
Not preservedPower Query likes static values, which suits this: the output has no formulas. But it also means any helper formulas, calculated columns, or extra sheets in the source export are gone. If the export computed columns with formulas you still need, flatten them to values first with Formula to Value: /excel-tools/excel-formula-to-value
Column name doesn't match the header
No cells changedHeaders must match exactly, including case and spaces. A mismatch (e.g. trailing space Weight_kg from an export) converts nothing and the file is unchanged — and your DAX measure stays wrong. Sanitise stray header whitespace and recheck the column name if the output equals the input.
Numbers stored as text in the export
Partial parse errorExports sometimes write numbers as text, especially with thousands separators. parseFloat stops at the first non-digit, so "1,234" parses as 1. Power BI would have its own type issues with these too. Fix the column to real numbers first; the Format Inspector flags text-stored numbers: /excel-tools/excel-format-inspector
Factor of 1 (column already in base unit)
RejectedIf a column is already in the target unit, don't add a rule for it — a factor of 1 is rejected as a no-op. Only write rules for columns that actually need to change scale; leave already-correct columns alone.
Multi-sheet workbook
First sheet onlyOnly the first sheet is read; other tabs are dropped from the output. Point the converter at the sheet Power Query consumes, and move it to the first position if it isn't already. The result is a single-sheet Converted workbook.
Negative or zero values in a measure column
ConvertedThe tool converts any finite number, including negatives and zero — it doesn't validate sign or range. A bad -1 or stray 0 converts faithfully and will flow into your measure. Validate source data quality separately (the Error Locator finds error cells: /excel-tools/excel-error-locator).
Date or datetime cells in a targeted column
SkippedDate cells parse to NaN and are skipped, copied through unchanged — the converter won't accidentally turn a date into a number. Ensure your rule targets the numeric measure column, not an adjacent date column.
10-decimal rounding vs DAX precision
By designConverted values are rounded to 10 decimal places before being written. That's finer than report display precision, and DAX will aggregate the exact stored values. If you need a specific stored precision, round in Excel after conversion with ROUND().
Free tier
Pro requiredUnit Converter is gated to Pro and above; a Free account cannot run it regardless of file size. Production exports usually fit Pro (50 MB / 100,000 rows); very large fact tables may need Pro-media or Developer.
Frequently asked questions
Why do my Power BI measures return the wrong totals when units are mixed?
DAX SUM/AVERAGE ignore units — they add the raw numbers. A column with kg on some rows and lbs on others produces a total that's arithmetically valid but physically meaningless. Standardise the column to one unit in the source before import so the measure aggregates comparable values.
Can I just do the conversion with a DAX calculated column instead?
Only if every row carries a unit flag the DAX can branch on. If rows have no unit indicator, the model can't know which to convert — pre-converting in the source is the only reliable option, and it keeps unit logic out of the model entirely.
My column mixes units row by row with a 'Unit' column — can this tool handle it?
Not in one step. The converter applies a fixed factor to the whole column, not per-row logic. Split the rows by the Unit column first with the Conditional Splitter (/excel-tools/excel-conditional-splitter), convert the off-unit subset, then re-append so the column is single-unit.
Does the tool add a unit label column to the output?
No. It changes numeric values only. If your report needs a unit label, add a static column in Excel after conversion, or define it in Power Query — the converter never writes or edits text label columns.
Will the output have formulas that Power Query has to re-evaluate?
No — the output is values-only. Power Query loads clean static numbers with nothing to re-evaluate, and there are no source formulas to break on dataset refresh. If your source had formula columns you still need, flatten them first with Formula to Value (/excel-tools/excel-formula-to-value).
Can I convert currency columns (e.g. GBP to USD) for a report?
Yes — use × Multiply with the rate (e.g. 1.27 for GBP→USD). But a static factor is a snapshot, not a live rate; for time-varying exchange rates, a Power BI relationship to a daily-rate table is more accurate than a one-off conversion. Use this tool for a fixed-rate normalisation.
Can I keep the original unit for drill-through detail?
Yes. Name a New col name so the converted value lands in a new column and the raw reading stays. Use the standardised column in measures and surface the original on a drill-through or detail page.
Why didn't a column convert at all?
Almost always a header mismatch — the Column name must match exactly, including case and any trailing space an export added (Weight_kg ≠ Weight_kg). The other cause is numbers stored as text. Check the header and the cell type (Format Inspector: /excel-tools/excel-format-inspector).
Does it preserve other sheets in my workbook?
No. Only the first sheet is read and the output is a single-sheet Converted workbook. Point the converter at the sheet Power Query consumes, and move it to the first position if needed.
How large an export can I convert before import?
Pro handles 50 MB / 100,000 rows, Pro-media 200 MB / 500,000, Developer 500 MB / unlimited. Free can't run the tool. It processes one file per run, so convert each regional file separately before appending in Power Query.
Is my operational data uploaded?
No. SheetJS parses the file in your browser; pre-publication operational data never reaches a server. The conversion is fully local and you download the result directly.
Can I script this into a refresh pipeline?
Yes. GET /api/v1/tools/excel-unit-converter returns the schema (the rules option, formats, output type). Execution is runner-backed — files never reach JAD; the paired @jadapps/runner converts locally and returns the XLSX, which a scheduled job can drop into the Power BI source folder before refresh.
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.