Concept
1. Locale and dates
Power Query reads text dates using your Windows region unless told otherwise. 05-04-2026 becomes 5 April on an Indian PC and 4 May on a US-settings PC — or an error for 25-04-2026 ("DataFormat.Error").
Fix: always set date types with a locale:
= Table.TransformColumnTypes(Source, {{"OrderDate", type date}}, "en-IN")
(Column icon → Using Locale → Date, English (India).) The same applies to decimals with commas (1.234,50 in European files → use "de-DE").
Also check: File → Options → Data → Power Query → Regional Settings → Locale for the workbook.
2. The automatic "Changed Type" step
Power Query adds a Changed Type step that lists every column by name. If the source later renames or drops a column, refresh fails: "The column 'Amount' of the table wasn't found."
Fixes:
- Delete the automatic step and set types yourself after Remove Other Columns.
- Turn it off for new files: File → Options → Query Options → Data Load → Type Detection → Never detect column types.
- Keep column-name-dependent steps few and late.
3. Hard-coded file paths
Source = Folder.Files("C:\Users\Divyesh\Desktop\data") breaks on every other PC. Use a parameter cell:
- In a sheet, type the folder path in a cell, name it
FolderPath(Name Box). - New blank query
pFolder:
= Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1]
- In the source step:
= Folder.Files(pFolder).
Now the user changes one cell, not the query. (Or Home → Manage Parameters → New Parameter.) For shared teams, keep data on SharePoint/OneDrive and use From SharePoint Folder so paths are the same for everyone.
4. Formula.Firewall and privacy levels
Combining data from different sources (a named cell + a folder, a web API + a file) can show "Formula.Firewall: Query … references other queries or steps…". Fixes: set privacy levels (Data → Get Data → Data Source Settings → Edit Permissions → Organizational for your own sources), or for trusted personal files: Query Options → Privacy → Ignore the Privacy Levels. Keeping the parameter in its own query (as above) usually avoids it.
5. Errors hidden in rows
A column with a few bad values shows Error in those cells; loading drops them silently into an error count ("12 errors"). Check: View → Column quality (Valid / Error / Empty %). Handle with Transform → Replace Errors, or Home → Keep Rows → Keep Errors in a check query to see the bad rows.
6. Other classic traps
| Trap | Fix |
|---|---|
| Preview shows 1,000 rows only — profile looks fine, full data isn't | Column profiling based on entire data set (status bar at the bottom) |
| Null vs empty text | Replace "" with null before Fill Down or null checks |
| Merge returns no matches | type mismatch or case/space differences (Lesson 5) |
| Numbers as text from CSV | set type; Trim first |
| Sorting doesn't stick | sort in the final step, or sort in the pivot instead |
| Huge file size | load to Data Model / connection only, not to sheets |
7. A pre-handover checklist
- No absolute paths (parameter cell or SharePoint)
- Dates typed with locale
- No unwanted automatic Changed Type steps
- Column quality shows 0% errors (or errors handled)
- Steps renamed meaningfully
- Refresh All works on a second PC
Common mistakes
Testing only on your own PC. Ignoring "N errors" after load. Leaving the first auto-generated type step in place.