"The report worked perfectly in development. On Monday morning it threw four different errors and nobody knew why. That is the moment you realise you never actually understood your data pipeline." — Power BI Advanced batch, Week 3

Data import errors are not random. Each error type has a specific cause, a predictable trigger, and a repeatable fix. The problem is that most candidates learn Power BI by building reports that work — so they never develop the mental model for diagnosing reports that break. This lesson builds that diagnostic model across four error categories: web authentication, data load errors, file path errors, and scheduled refresh failures.

🗺️
Think of it like a hospital triage system

When a patient arrives in A&E, staff do not immediately treat every symptom at once — they identify the category of problem first (cardiac? respiratory? trauma?), then apply the protocol for that category. Power Query error diagnosis works the same way. The error message tells you the category. Once you know the category, the fix follows a known protocol: wrong auth method → change authentication type; type mismatch → change column data type; missing file → update source path; expired credentials → re-enter gateway credentials. Pattern first, fix second.

The 4 Error Categories — Quick Reference

Error Category Where It Appears Root Cause First Fix Step
Web Authentication Power Query Editor at connection time Wrong auth method selected or credentials not entered Change authentication type in data source settings
Data Load / DataFormat.Error Yellow warning icon on query in Power Query Type mismatch, null values in non-nullable column, schema change Inspect the errored rows; check the Transform step that applied the type
File Path / DataSource.Error Red X on query or at report open File moved, renamed, or network share unavailable Right-click Source step → Edit Settings → update path
Scheduled Refresh Failure Power BI Service refresh history Expired credentials, gateway offline, schema changed in source Check refresh history error detail; update credentials or gateway

Web Authentication Errors

Web authentication errors appear when Power BI cannot verify your identity against a web-based data source — a public API, a SharePoint list, or a website table. The three most common authentication types you will encounter:

  • Anonymous: Use for fully public data — no credentials required. Example: Wikipedia tables, public JSON APIs.
  • Basic (username + password): Use when the website or API requires HTTP Basic Auth. Enter the username and password when prompted.
  • OAuth 2.0 / Organizational Account: Use for Microsoft 365 sources (SharePoint, Dataverse, OneDrive) — sign in with your work account. Do not use "Anonymous" for these sources; it will always fail.

Privacy Level conflicts are a second cause of web auth failures. If two queries from different privacy levels try to combine data (e.g., a public web source and an internal SQL Server), Power BI may block the combination and show a "Formula.Firewall" error. Fix: set the privacy level for the public source to "Public" and the internal source to "Organisational" in File → Options → Privacy.

Data Load Errors (DataFormat.Error)

Data load errors appear as yellow warning icons on queries in the Power Query Editor. The most common trigger is an explicit Change Type step that tries to convert a column to a type the data cannot support.

❌
Type Mismatch
A date column contains a cell with the text "N/A". When Power Query applies the "Date" type, that row throws DataFormat.Error. Fix: Replace "N/A" with null before the Change Type step.
❌
Missing or Renamed Column
The source renamed "SalesAmt" to "SalesAmount". Every downstream step that referenced "SalesAmt" by name now throws an error. Fix: Update the column reference in the affected step, or add a Rename Columns step before it.
✅
Error Handling in Power Query
Three options in the right-click menu on an error column: Remove Errors (delete affected rows), Keep Errors (isolate them for inspection), Replace Errors (substitute a fallback value). Use Replace Errors for nullable columns where null is acceptable.

File Path Errors (DataSource.Error)

File path errors occur when Power BI cannot locate the file that was specified when the query was originally created. The source step in Power Query stores an absolute path — if the file moves, the path is stale.

To fix: In Power Query Editor, right-click the Source step in the Applied Steps pane → Edit Settings → update the file path to the new location. Click OK and refresh.

Prevention: For Excel or CSV files that are frequently moved, use a Power Query parameter to store the file path. Changing one parameter value then updates all queries that use it — no manual step editing required.

Enterprise pattern: store file paths as query parameters (Home → Manage Parameters → New Parameter). When the file moves to a new folder or SharePoint library, update the parameter — all connected queries update automatically.

Scheduled Refresh Failures (Power BI Service)

Scheduled refresh failures are the most impactful error type in production — they mean your stakeholders are looking at stale data and may not know it. The refresh history in Power BI Service shows the exact error. Common causes and fixes:

Error in Refresh History Cause Fix
Credentials expired / invalid Service account password changed or OAuth token expired Settings → Datasets → Data source credentials → Edit credentials
Gateway not reachable Gateway host server offline or gateway service stopped Start gateway service on the host; verify Azure Service Bus connectivity
DataFormat.Error in Service Source schema changed (column renamed or type changed) since last Desktop publish Open .pbix in Desktop, fix the query, republish the dataset
Capacity limit exceeded Dataset too large for refresh memory on shared capacity Reduce model size via aggregations, or move to Premium/Fabric capacity

3 Mistakes That Make Errors Worse

  • Ignoring yellow warning icons in Power Query Editor: Power Query surfaces errors at the query level as yellow icons — but it still loads the dataset with errors silently replaced by null. Analysts often ship reports without noticing that 5% of rows errored out. Always check the error count in the bottom status bar before publishing.
  • Hard-coding absolute file paths: A path like C:Users aushanDocumentsSales_2025.xlsx works on your machine and breaks on every other machine and in the Service. Use SharePoint, OneDrive for Business, or parametrised paths for any files that need to refresh in the cloud.
  • Not checking refresh history after publishing: A dataset that refreshed successfully in Desktop may fail in Service because of credential configuration or gateway issues. Always manually trigger a refresh immediately after publishing a new dataset and verify the green tick in refresh history before calling the deployment done.

Quick Knowledge Check

Q1. A Power Query step that changes a date column type throws DataFormat.Error on several rows. What is the most likely cause?

Show Answer

The date column contains values that cannot be parsed as dates — such as text strings like "N/A", "TBC", or blank cells that resolve to empty strings rather than null. Fix: add a Replace Values step before the Change Type step to convert those invalid values to null, which the Date type accepts.

Q2. A Power BI report connects to an Excel file at path C:Users aushandatasales.xlsx. After publishing to Power BI Service, the scheduled refresh fails with DataSource.Error: File not found. What is the correct fix?

  • A) Change the data source credentials in Power BI Service settings
  • B) Move the file to the same path on the gateway server
  • C) Store the file in SharePoint or OneDrive for Business and update the data source to use the cloud URL
  • D) Increase the refresh timeout setting in the dataset configuration
Show Answer

C. A local file path (C:Users...) is only accessible on the machine where the file lives. Power BI Service cannot reach local file paths. Move the file to SharePoint or OneDrive for Business, and update the Power Query Source step to point to the cloud URL. Option B (gateway server path) is a partial fix but fragile — cloud storage is the enterprise standard.

Q3. After publishing a dataset that refreshed fine in Desktop, the first scheduled refresh in Power BI Service shows "Credentials are required to connect to the SharePoint source." What is the correct next step?

Show Answer

Configure the data source credentials in Power BI Service. Go to the dataset → Settings → Data source credentials → Edit credentials. For SharePoint / Microsoft 365 sources, select "OAuth2" and sign in with your organisational account. Desktop refreshes use your local identity; Service refreshes require explicitly stored credentials.

5 Things to Remember
  • Four error categories, four diagnostic paths — web auth, data load (DataFormat.Error), file path (DataSource.Error), scheduled refresh failure. Identify the category first, then apply its protocol.
  • Yellow icons in Power Query are not warnings — they are errors — the affected rows are silently dropped or replaced with null. Always check error counts before publishing.
  • Local file paths break in the Service — use SharePoint, OneDrive for Business, or parametrised paths for any data source that needs cloud refresh.
  • Credentials in Desktop ≠ credentials in Service — after publishing, always configure data source credentials explicitly in Power BI Service settings.
  • Fix schema changes in Desktop, republish — when a source renames or removes a column, you cannot fix it in the Service. Open the .pbix, update the affected Power Query steps, and republish the dataset.
📚 Resources