Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Elsie14
Advocate I
Advocate I

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?

11 REPLIES 11
t-onnyy
New Member

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
Community Champion
Community Champion

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? Mark my post as a solution!

Proud to be a Super User!




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
Not applicable

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. 

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?

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

 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"

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.

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?

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.