How to find text-stored dates in excel before they break your power bi report
- Step 1Audit the source before connecting Power BI — Do this on the source
.xlsxbefore (or after fixing, before re-) refreshing the dataset. The goal is a date column with zero text cells so Power Query infers Date, not Text. - Step 2Save the source as .xlsx — The inspector reads
.xlsxonly. If your source is a CSV that Power BI imports, open it in Excel and Save As an Excel Workbook to scan it — a CSV has no per-cell type info for the audit. - Step 3Drop the workbook onto the tool — SheetJS parses it in your browser. The scan reads every sheet, so a hidden staging or calendar sheet feeding the dataset is audited too. Nothing uploads.
- Step 4Read the date-mismatch report — Lines labelled 'looks like date' are the cells that would force the column to Text in Power Query. Lines labelled 'looks like number' are measure cells that wouldn't aggregate. The count panel shows the total; the first 100 lines show inline.
- Step 5Fix the source date column in Excel — Convert the flagged text dates to real dates: select the column → Data → Text to Columns → Next → Next → choose Date with the correct D/M/Y order → Finish. For text measure numbers, convert with the General format. The inspector pinpoints them; Excel does the typing.
- Step 6Re-scan, then refresh Power BI — Re-scan the fixed workbook; 'No format mismatches found' means the date column is fully real dates. Then in Power BI refresh the dataset — Power Query types the column as Date, the auto hierarchy returns, and DATESYTD/SAMEPERIODLASTYEAR resolve instead of returning blank.
How text dates break Power BI — and what the inspector flags
The downstream Power BI failures caused by text-stored dates, mapped to the detector's output.
| Power BI symptom | Cause | Inspector flags |
|---|---|---|
| No auto date/time hierarchy on the column | Power Query inferred the column as Text | Each text date as 'looks like date' |
| DATESYTD / TOTALYTD return blank | Time-intelligence needs a Date column | The text dates forcing Text type |
| Date slicer shows a flat string list | Column isn't a real date | The offending date cells |
| Two '2024' groups won't combine | Mix of real dates and text dates | Only the text dates (real ones skipped) |
| Measure column won't sum in a visual | Value cells stored as text | Those cells as 'looks like number' |
15 Jan 2024 still breaks the column | Spelled month not matched | NOT flagged — convert manually |
Date shapes the detector matches
The date pattern matches a 1-4 digit / 1-2 digit / 1-4 digit shape with /, -, or . separators. Day/month order is not interpreted.
| Text value | Flagged as date? | Note |
|---|---|---|
2024/01/15 | Yes | ISO-ish slash form |
01-02-2024 | Yes | Dash form (order ambiguous) |
1.2.2024 | Yes | Dot separator allowed |
15/03/24 | Yes | 2-digit year allowed |
2024-01-15T09:30 | No | Time component breaks the pattern |
15 Jan 2024 | No | Spelled month not matched |
Jan-24 | No | Letters break the pattern |
20240115 | No — flagged as NUMBER | Pure digits match the numeric pattern |
Cookbook
Real pre-Power-BI-refresh audits: find the text dates, fix the source, then refresh so date intelligence works.
Date hierarchy missing after import
Power BI didn't show the date hierarchy because three rows had text dates, forcing the whole column to Text in Power Query. The inspector flags those three so the column types as Date after the fix.
Power BI: 'Date' column has no date hierarchy.
Source sheet 'Orders', column A 'Date':
most cells are real dates, but:
A120 = 2024/02/03 (text)
A255 = 2024/05/19 (text)
A398 = 2024/08/01 (text)
Inspector report:
Found 3 format mismatch(es):
[Orders] A120: stored as text, looks like date ("2024/02/03")
[Orders] A255: stored as text, looks like date ("2024/05/19")
[Orders] A398: stored as text, looks like date ("2024/08/01")
Fix those 3 -> refresh -> hierarchy appears.DATESYTD returns blank
A YTD measure returns blank because the date column is text. The inspector confirms the date cells are text so you convert them before the measure can resolve.
Measure: Sales YTD = TOTALYTD([Sales], 'Data'[Date])
Result in visual: (blank)
Source sheet 'Data', column B 'Date':
B2 = 2024/01/05 (text)
B3 = 2024/01/06 (text)
Inspector report:
Found 2 format mismatch(es):
[Data] B2: stored as text, looks like date ("2024/01/05")
[Data] B3: stored as text, looks like date ("2024/01/06")
Convert B:B to real dates -> refresh -> TOTALYTD resolves.Two '2024' buckets in a slicer
A year slicer shows two 2024 entries that won't combine because half the dates are real and half are text. The inspector flags only the text ones.
Slicer shows: 2024 (real dates) and 2024 (text dates)
Source sheet 'Tickets', column C 'Created':
C2 = real date 2024-03-01
C3 = 2024/03/02 (text)
C4 = 2024/03/03 (text)
Inspector report:
Found 2 format mismatch(es):
[Tickets] C3: stored as text, looks like date ("2024/03/02")
[Tickets] C4: stored as text, looks like date ("2024/03/03")
Convert C3:C4 -> refresh -> single 2024 bucket.Date and measure both wrong in one source
The same source has text dates AND text measure numbers. One scan catches both, labelled separately so you fix each with the right Excel action.
Inspector report (Sheet 'Fact'):
[Fact] A2: stored as text, looks like date ("2024/01/10")
[Fact] A3: stored as text, looks like date ("2024/01/11")
[Fact] D2: stored as text, looks like number ("4500")
[Fact] D3: stored as text, looks like number ("3900")
Fix:
- Column A -> Text to Columns -> Date (YMD)
- Column D -> Text to Columns -> General
Then refresh: date hierarchy + Sum of measure both work.ISO datetime with a time component slips through
A value like 2024-01-15T09:30:00 has a time part, which breaks the date pattern, so it isn't flagged. Power BI may still mis-type it. Strip or split the time, or rely on Power Query's parser, then re-scan the date-only form.
Source column E values: 2024-01-15T09:30:00 (text)
First scan: NOT flagged (the 'T09:30:00' breaks the
date pattern). Misleading if you expected it caught.
Options:
- In Excel, split date and time, keep the date part
- Then re-scan: [Log] E2: stored as text, looks like date
("2024-01-15") -> convert -> refresh.Edge cases and what actually happens
Source is a CSV, not XLSX
RejectedThe inspector reads .xlsx only — it compares stored type against value, which needs XLSX metadata. A CSV is all text. Open it in Excel and Save As an Excel Workbook to audit it before Power BI imports it.
Spelled-out month dates
Not detected15 Jan 2024 and January 2024 don't match the numeric date pattern, so they're not flagged even though they'll still break Power BI's date typing. Convert these in Excel (DATEVALUE or reformat) separately; the detector only catches numeric date shapes.
ISO datetime with a time component
Not detected2024-01-15T09:30:00 includes a time part that breaks the date pattern, so it isn't flagged. If Power BI mis-types such a column, split or trim the time portion first, then re-scan the date-only value to confirm it's now caught.
Ambiguous day/month order
Flagged (order not judged)03/04/2024 is flagged as 'looks like date' but the detector makes no decision about 3 April vs 4 March. When you convert in Excel, pick the D/M/Y order that matches your source locale, and verify Power BI's locale matches — otherwise dates land in the wrong month after refresh.
Pure-digit date like 20240115
Flagged as number, not date20240115 is pure digits, so it matches the numeric pattern and is reported as 'looks like number', not 'looks like date'. Power BI won't read it as a date either. Convert it to a real date in Excel (or via DATE/parsing); the report label is a hint it's a packed date, not an amount.
Real dates mixed with text dates
Flagged (text ones only)The detector flags the text dates and skips the real ones — exactly what you want, because the real dates are fine and only the text ones force Power Query to Text. Convert the flagged cells so the whole column types as Date.
Hidden calendar/staging sheet
SupportedIf your Power BI model references a hidden calendar or staging sheet, the scan audits it too — every sheet is checked. Text dates there appear in the report with the sheet name, so a hidden dimension table doesn't silently break time intelligence.
Report capped at 100 inline lines
Truncated displayA large fact table with many text dates exceeds the 100-line inline cap; the report appends '... and N more.' and the count panel shows the true total. Fix the whole date column at once in Excel (Text to Columns → Date) rather than cell by cell.
Date column already typed correctly
PreservedCells already stored as real dates aren't flagged — they type correctly in Power Query already. A clean scan means the column will infer as Date, the hierarchy appears, and time-intelligence DAX resolves. That all-clear is your refresh green light.
Source workbook over the tier limit
Rejected (size limit)Free tier caps the Excel family at 5 MB / 10,000 rows. A large fact-table workbook is rejected before scanning. Upgrade to Pro (50 MB / 100,000 rows) or Pro-media (200 MB / 500,000 rows), or split the source by sheet and scan each before the refresh.
Frequently asked questions
Why does my Power BI date hierarchy not appear?
Because Power Query inferred the column as Text rather than Date — usually because some source cells are dates stored as text. When even a few cells are text, Power Query types the whole column as Text, and the auto date/time hierarchy never generates. This inspector finds those text-date cells so you fix the source before refreshing.
Does the tool convert the dates for me?
No — it's a detector. It reports every cell that's a date stored as text (and every text-stored number). You convert them in Excel before the refresh: select the date column → Data → Text to Columns → Date with the right D/M/Y order → Finish. The inspector pinpoints the cells so the conversion is precise.
Why do my DATESYTD / SAMEPERIODLASTYEAR measures return blank?
Time-intelligence DAX functions require a column typed as Date. If text dates forced Power Query to type the column as Text, those measures have no real date axis to work on and return blank. Convert the flagged text dates in the source, refresh, and the measures resolve.
Should I scan before or after the Power BI import?
Before. The type problem lives in the source Excel file; once Power Query reads it as Text and you build measures, you're working on the wrong type. Audit the source .xlsx, fix the flagged dates, then connect or refresh Power BI so the column types as Date from the start.
What date formats does the detector catch?
Numeric date shapes with /, -, or . separators: 2024/01/15, 01-02-2024, 1.2.2024, 15/03/24. It does NOT catch spelled-out months (15 Jan 2024), ISO datetimes with a time part (2024-01-15T09:30), or pure-digit packed dates like 20240115 (those are flagged as numbers). Those need manual conversion.
It flagged a date as 'looks like number' — why?
A pure-digit value like 20240115 matches the numeric pattern, so it's labelled 'looks like number'. It's actually a packed date stored as text. Power BI won't read it as a date either — convert it to a real date in Excel. The label is a hint that the cell isn't a true amount.
Does it check my measure (value) columns too?
Yes. The same scan flags text-stored numbers as 'looks like number'. So a measure column Power BI should aggregate gets audited alongside the date column — convert both before the refresh so visuals sum correctly and dates type as Date.
Will it scan a hidden calendar or staging sheet?
Yes. The scan walks every sheet in the workbook, including hidden ones. If your Power BI model pulls from a hidden calendar or dimension sheet, any text dates there are reported with the sheet name so they don't silently break time intelligence.
Is my dataset uploaded anywhere?
No. The scan runs entirely in your browser with SheetJS. Dates, measures, and sheet names never leave your machine. Free tier needs no upload and no account; only an anonymous usage counter may be recorded for signed-in users, never file content.
What file types does it accept?
.xlsx only. The audit needs the per-cell type metadata XLSX stores to separate text from date/number. CSV has no type info — save a CSV source as an Excel Workbook first.
How large a source can I scan before a refresh?
Free tier: 5 MB / 10,000 rows. Pro: 50 MB / 100,000 rows. Pro-media: 200 MB / 500,000 rows. Developer: 500 MB / unlimited. Over-limit fact tables are rejected before scanning; split by sheet if needed.
What related tools help prep an Excel source for BI?
The date standardizer normalises mixed date formats into one consistent shape, the unpivot tool reshapes wide tables into the tall format BI models prefer, and the formula-to-value tool flattens volatile formulas to static values before a refresh. Use this inspector first to catch text dates and numbers.
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.