How to find text-stored dates in excel before they break your power bi report
- Step 1Export the Excel data source for Power BI — Download the .xlsx file used as a Power BI data source.
- Step 2Scan for text-stored dates — Upload and run the Format Inspector. Every cell matching a date pattern but stored as text is listed.
- Step 3Fix with the Date Standardizer, then refresh Power BI — Run the JAD Date Standardizer to convert flagged cells, then re-import into Power BI. Date hierarchies will work correctly.
Frequently asked questions
Why does Power BI's date hierarchy fail on text-stored dates?+
Power BI's date hierarchy is only created for columns Power BI detects as Date or DateTime type. Text columns — even if they look like dates — do not trigger automatic hierarchy creation.
Can Power Query fix this automatically during import?+
Yes. Power Query's 'Change Type → Date' step converts text dates during import. But if the format is non-standard, Power Query may reject them. Pre-standardising in the source is more reliable.
After fixing text dates in the source, does the Power BI date hierarchy appear automatically?+
Yes. Refresh the Power BI data source. Power BI will detect the Date type and create the year/quarter/month/day hierarchy automatically.
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.