Data shows in Power Query, but not in Data view

[kboud3]

My source excel file has 30 rows and 10 columns. In the source file, all rows and columns are filled with data. However, when I connect to the file and build out a chart, some data is missing. In the Data view, it shows the last row having only 7 of the 10 columns populated with data, but when I go to transform data and open the power query, it shows that all columns are populated. I refreshed multiple times. How can I get the data to populate in PBI when I know for sure that there is data there?

[JWON]

I have the same issue. Data can be seen in the Power Query editor but the worksheet is blank except for dates and headers. How do I fix this?

[RossEdwards]

Is your query subject to query folding?  I've had situations where my query steps are correct but the folded query is not the same as the steps because it writes bad SQL code.

The solution is to find a fake step that you can add to break query folding.

[RossEdwards]

I have run into a similar problem in the past with other sources.  The data preview sometimes lies to you, so if you are getting different results it usually means there is a problem in the source that the query steps preview isn't picking up.

Since its the last row thats missing data, the issue will likely be present on that last row.  Since you are only getting the first 7 columns, this indicates that the issue will be in the cell of that last row in the 8th column.

Open up your excel file, check that cell (and the ones either side) and see if there are any excel errors or funny characters.

[Idrissshatila]

Hello @kboud3 ,

this is weird, maybe there's something in power query that is preventing the data to load.

if you didn't find anything that is blocking data from showing, then try to re-import the excel sheet again.

[kboud3]

Thanks for the reply. I deleted the spreadsheet from the model and reconnected and it is the same issue.