cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elsie14 Regular Visitor
Regular Visitor

Expression.Error: The column '<ColumnTitle>' of the table wasn't found

I have a question regarding refreshing my dataset.

 

My first dataset contained a fixed amount of colums for which I edited a couple of queries. In my refreshed/new dataset a column was added. When I refreshed the dataset, I got the error message which is mentioned in the title.

 

It seems that the most right column does not appear in the new dataset causing the column not to be found. It looks like Power BI sets a fixed amount of columns according to the original datasets and if columns gets added in the refreshed dataset, it just forgets to import the most right column. Something like this:

 

Original dataset:

ColumnHeader1      ColumnHeader2     ColumnHeader3     ColumnHeader4     ColumnHeader5

 

New Dataset:

ColumnHeader1     ColumnHeader2     NewlyAddedHeader    ColumnHeader3     ColumnHeader4

 

Which results in ColumnHeader5 to be erased en consequently not refreshed right. 

 

My question is: how can I fix this without having to delete all my query edits?

8 REPLIES 8
Super User
Super User

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

You have some step in your query that explicitly references the missing column by name. Find that step and correct it. I couldn't begin to guess what it is without seeing the query. It could be a step where you renamed or re-ordered columns, changed data types, removed other columns, replaced values...

 

If it's not obvious by browsing through the query steps, open the advanced editor on that query, copy and paste the entire query into a text editor and do a Find for the column name.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Elsie14 Regular Visitor
Regular Visitor

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

I have found the error: In the advanced editor, when retrieving the source, the column width was fixed to 19. Therefore it only imported the first 19 columns. However as explained above I had one new column so the last column would disappear. Another question then: how can I alter the query edit so that in the future new columns will always have the same query edits as the other columns?

Moderator v-yuezhe-msft
Moderator

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

Hi @Elsie14,

Could you please post the full code in your Advanced editor? Based on my test, Power BI Desktop will not set a fixed amount of columns, when I add a new column in data source, I can successfully refresh the dataset in Power BI Desktop.

Besdies, could you please describe more details about your requirement that new columns have the same query edits as the other columns?


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Elsie14 Regular Visitor
Regular Visitor

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

The first line in my text editor is:

 

Source=Csv.Document(File.Contents("<FileDestination>"),[Delimiter=",", Columns=19, Encoding=65001, QuoteStyle=QuoteStyle.None]),

 

When I removed "Columns=19" from this, the dataset was refreshed correctly.

 

In the other query edits I changed the datatype of particular columns. However I want Power BI to also automatically change the datatype of the newly added column without having to add new query edits. Is this possible?

Moderator v-yuezhe-msft
Moderator

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

Hi @Elsie14,

As stated in this article, when you load data into Power BI Desktop, it will attempt to convert the data type of your source column into a data type that better supports more efficient storage, calculations, and data visualization.

If you don’t get desired data type of the newly added column in Power BI Desktop, you would need to manually change its data type in the Query Editor, or in Data View or Report View.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kvollmer New Member
New Member

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

Thanks!  I had the same problem and removing the columns reference worked for me as well!

SurendraP Frequent Visitor
Frequent Visitor

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

Thank You. I had same problem, Resolved it by removing the columns from Advanced Editor. It had long list of columns so i had to deal this in Notepad. 

johnconnor92 Regular Visitor
Regular Visitor

Re: Expression.Error: The column '<ColumnTitle>' of the table wasn't found

I faced the same problem just now. the column "snapshot" that Power BI claims couldn't be found is actually there, in every single file of the folder I designated. But when I removed the column limiter from advanced editor, the data refresh worked. The refresh worked just fine up until yesterday, and nothing changed.

 

I'm on the July 2018 update version.