Forum Discussion
Data "lost" in model view vs. Power Query Editor
Hi I'm new to Power BI and will describe things in an odd way. Hope you will understand my question.
I have an existing DB with it's created data model. There is a mistake in the data (here: missing the data of Feb. and Mar., only containing Jan.).
But looking at the created table in the Power Query Editor, this table contains all the data. I did manage to fix it by removing one line (here it originally removed one not needed column [Table.RemoveColumns()]. I want to understand what can be possible reasons for that behavior.
Thank you in advance.
Hi worldlife
This behavior usually happens because of Query Folding, which is how Power Query translates your transformation steps into a single request sent back to your database. When you include a step like Table.RemoveColumns(), Power Query alters that database request; if that specific column is tied to upstream filters, joins, or views in the source database, dropping it can accidentally cause the database to withhold certain data rows, like your February and March records.
Another common reason is preview caching. The Power Query Editor only displays a limited preview of your data and caches it to keep the interface fast, meaning you might have been looking at an outdated snapshot that only captured January. When you deleted that column-removal line, you forced Power Query to completely clear its cache and pull a fresh evaluation from the database, which finally brought in the missing months.
5 Replies
- danextian
Super User
Hi worldlife
Power Query only displays a preview of the data, which may not always reflect what is actually loaded into the model. As long as no filters have been applied in the query editor and the data has not changed during refresh, all rows should still be imported. It may help to sort or filter the data in the table view, as the data you are looking for could already be there.
Hi danextian and thank you for your input.
I wish that would be the answer but that is not the case.
I recently received updated data with the month of April added and here it was somehow not possible to get that into the dashboard. But out of curiosity I changed that code back to it's previous form and now it does show all months.
Assuming my specific case has your described problem, then I would not have been able to get the data from Feb and Mar after rolling back the code change (since that was the problem in the first case).- Poojara_D12
Hi worldlife
This behavior usually happens because of Query Folding, which is how Power Query translates your transformation steps into a single request sent back to your database. When you include a step like Table.RemoveColumns(), Power Query alters that database request; if that specific column is tied to upstream filters, joins, or views in the source database, dropping it can accidentally cause the database to withhold certain data rows, like your February and March records.
Another common reason is preview caching. The Power Query Editor only displays a limited preview of your data and caches it to keep the interface fast, meaning you might have been looking at an outdated snapshot that only captured January. When you deleted that column-removal line, you forced Power Query to completely clear its cache and pull a fresh evaluation from the database, which finally brought in the missing months.
Hi Poojara_D12
That explains a lot, thank you for that!
Is there a way to add a line or to force a cache-clearing and grabbing the data?
Currently I would have to comment in/out the line to make it work.- Poojara_D12
Hi worldlife
If you want a permanent way to force Power Query to clear its cache and grab fresh data without commenting lines in and out, you can append a dummy parameter or a dynamic timestamp step to your source connection. For example, if you are using a native SQL query or web source, adding a step that references a changing value (like DateTime.LocalNow()) inside an unused metadata field or a dummy query string forces the Power Query engine to completely bypass its local preview cache and evaluate a fresh request every single time you hit refresh.
Alternatively, you can change your workflow to avoid the Table.RemoveColumns() step entirely if it keeps breaking query folding. Instead of removing the unwanted column, select only the columns you actually need to keep, right-click a header, and choose Remove Other Columns (Table.SelectColumns()). This explicitly declares the exact schema you want to the database, which usually keeps query folding intact and prevents the data from getting "lost" or stuck in an old cache state to begin with.
