Power BI Column Not Found errors are one of the most common and frustrating errors you will run across when developing reports and dashboards. We explain how to fix the most common causes and some not so common causes to prevent this error from happening.
What Causes a Column of the Table Wasn’t Found Error?
This error is caused by Power BI being unable to identify a column that was previously available. It typically occurs after updating the source file that a Power BI report is based on. Either a column name was changed, a column was removed, or a column was added which pushed the referenced Power BI column out of the relevant range of the data source.
Delete and Re-add the Data Types Step
The most common cause of a column not found error in a Power BI Power Query is the Change Data Type Applied Step. Power BI will automatically attempt to determine if a column is text or a number so it will know in future steps how to apply formulas and functions.
Power BI treats numbers and text differently. For example, Power BI will know how to add two numbers together. It will not know what to do if you try to add a number and a string or text field together. This will cause another error.
Because data types are so important in Power BI, the software will automatically attempt to detect and apply a data type to each column in your data. Unfortunately, Power BI detects the column data types and assigns them based on column name. If a column name changes or no longer exists, you will receive an error message that the column is not found.
This Applied Step in Power Query is notated as Changed Type.
If you right click on a dataset from the dashboard design screen, and choose Edit Query you will be brought to the Power Query Editor. This is where you can updated the steps for the data transformation and data prep that Power BI is performing.
Click the X to the left of the Changed Type Step to Remove It.
This removes all of the automatic data type assumptions that Power BI has made. To reset them, click on:
Transform > Detect Data Type
By removing and re-selecting the Detect Data Type option, the column not found error will be resolved in most cases. If this does not fix your problem, continue reading on for additional common causes and fixes to this problem.
Check Column Names in the New and Previous Dataset
Power BI looks for the exact matching name when updating a data source. If a source document has updated column names, Power BI can get stuck looking for the old one or not recognize that a new one exists.
Changing column names of data input occurs most often when working with a .csv or an Excel File.
The quickest fix is to open the source file and update the column name.
Fixing the source input manually will mean that the dashboard will be harder to automate down the road. It’s always best to use source data that comes from a source system. Typically reports that are automatically generated have consistent column names every single time you run a report or export data.
There are a few common causes of names to be different that you should check for.
- Plural vs singular column header names such as a column named Value vs. Values
- Extra white space in a column header. This is very common when using Excel as a data source.
Note: If you are using Excel files, a quick way to identify if the columns have different names is to copy and paste the headers into the same workbook and use an = formula to check if the two column names match. The setup will look like the screenshot below. A value will return TRUE if it matches and FALSE if it doesn’t.
This technique will help you identify if there is white space. White space is usually trailing spaces that you can’t see at the beginning or end of a word. You can get rid of it by using a TRIM formula. Other hidden characters can be removed with a CLEAN function in Excel.
Best Books to Learn Power BI
From Beginner to Advanced, Power BI books are great ways to learn new techniques and are great reference guides for mastering DAX.
Pop! Automation may receive affiliate commissions for purchases through these links at no additional cost to you.
Check that a Column Wasn’t Deleted
If a column previously existed but it was deleted, then you will get a column not found error. You can use a method like the one above of copying a row of Excel headers between two Excel workbooks and line them up. If you notice that all of the sudden the columns don’t align than that field was removed.
If the data is no longer needed you could add it to the new data source as a placeholder, or use the Power Query Advanced editor as noted below. You would need to find the section of text that references that column and delete that part of the query.
Note: Power BI generates text queries when you’re adding query steps. The advanced editor lets you edit those queries. If it seems intimidating to edit Power Query, try leveraging AI tools like ChatGPT for Power BI.
Check if a Column Was Added
Similar to how a column can be deleted, a column can also be added between datasets. Power BI has mostly addressed this issue with Excel .xlsx files, but it can still occur when working with .csv files.
To fix this problem, click on the Advanced Editor on the toolbar of Power BI.
There will be a section at the top source line that says Columns= if you update this to match the number of columns in your dataset, Power BI will find your data source.
Usually someone will add a column in the middle of their dataset, and it pushes a previously referenced Column outside of the Source range. Updating this will bring it back into range and fix the error.
Finding a Power BI Column Not Found Error in Applied Steps
Another way to approach troubleshooting this error is to step through the Applied Steps. It’s possible that the data source you are working with is correct, and there’s a step somewhere in the middle of the applied steps that hasn’t been updated to reflect the change in starting columns or fields.
Click through the applied steps.
Go step-by-step and see which one gives you an error. Many times it’s caused by the Change Type selection. This is the selection that switches text to numbers, updates formatting, controls the number of decimals shown etc. It references each specific column, so if a column no longer exists or the name changes it will error out on this step.
In our example below, we can get through the Promoted Header section before getting an error on the Changed Type applied step.
To fix this issue, you can delete the Promoted Headers step by clicking the X on the left of that step. Then click on Transform > Detect Data Type to re-create the step.
Detect Data Type will automatically assess the best data type for the column based on a sample of its contents.
Alternatively, you could use the Advanced Editor to update the data type of a single column at a time, or delete that section of code if you don’t want to delete everything and start over.
Note: If you need to modify promoted headers, you can also access the option under the Transform section of the Power Query Editor. You can also switch from promoting headers to demoting headers which allows you to trim the header names before re-promoting them to the top if you don’t want to open the source file.
Preventing Column of the Table Wasn’t Found Errors
Because these errors are typically caused by changes in the source dataset and most frequently occur when importing Excel spreadsheets or csv files we recommend getting closer to the source of the data.
A number of dashboards and reports we have worked on are based on data that comes from a specific person or department within an organization. While the person sending you data may have the best of intentions in mind, it’s very common that they will modify a report that they get out of the accounting or operational system.
If you can get the starting data from a source system, it’s much less likely to change. It might be messier, and require more steps in Power Query but it will be consistently messsy.
Another option when available is to skip the Excel or csv files and connect directly to the back-end of your source system. Power BI Supports Over 200 connectors and with some help with IT, you can skip running reports altogether.
The consistent applied steps in Power BI will ensure that data is clean, and eliminates manual intervention. It will also minimize the number of errors that you receive due to manual adjustments of source files.
Conclusion
Whether you’re using Power BI or Excel Power Query a column of the table wasn’t found error can be an annoying one. It often occurs when the schema or names of column change on incoming datasets. To minimize these errors we recommend doing as little transformations to data in Excel and take it from the source system.
Reports ran directly from an ERP or Accounting system may be more difficult to work with in Power Query but they are consistent. By using Power Query the steps to prep the data only have to be setup once and they become completely repeatable enabling you to automate more of your processes.