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:
ColumnHeader1 ColumnHeader2 ColumnHeader3 ColumnHeader4 ColumnHeader5
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?
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.
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?
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?
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?
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.
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.
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.