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

[Elsie14]

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?

[t-onnyy]

I found that some of the columns had "Spaces" after the code ended and once those got deleted seem to fix it. Since it was trying to pull from a column that technically "didn't exist".

[KHorseman]

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.

[SurendraP]

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. 

[Anonymous]

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. 

[Elsie14]

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?

[kcaldwell]

Same here - columns were fixed in the advanced editor, removed it, everything refreshed as expected.  Thanks for the tip.

[ktt777]

 Here are my advanced editor. But there is no column limiter  to remove. Any sugesstion ?

let
Source = Excel.Workbook(File.Contents("C:\Data2019\KPI 2019.xlsx"), null, true),
Global_Sheet = Source{[Item="Global",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Global_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year/Month", type date}, {"Region", type text}, {"Value", type number}, {"Category", type number}})
in
#"Changed Type"

[v-yuezhe-msft]

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

[Elsie14]

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?

[kvollmer]

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

[v-yuezhe-msft]

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