Forum Discussion
Refresh error due to blanks in key column
Hello!
I´m having troube refreshing my PBI Desktop model. It just contains data from an Excel file. I updated de Excel file and now it has less rows. Then I tried to refresh and this is what appears:
I believe it is considering as blanks the rows that does not exist now, with the update. I deleted them in Excel but it did not solve the problem.
Could anyone please help me with this? 🙂 Many thanks.
Saludos,
Pedro
Hello @pedrocordova
The Power Query query might detect more rows than necessary. Select the key column in Power Query, click the filter drop-down list, and delete the null or (blank) values.
That way you don't have to go back and edit the Excel file. The goal is to do 100% of the work in Power Query so you don't have to do manually and redo manual transformations.
2 Replies
- edhans
Community Champion
Hello @pedrocordova
The Power Query query might detect more rows than necessary. Select the key column in Power Query, click the filter drop-down list, and delete the null or (blank) values.
That way you don't have to go back and edit the Excel file. The goal is to do 100% of the work in Power Query so you don't have to do manually and redo manual transformations.
darentengmfsPost Prodigy
Anonymous
Issue is likely from your excel file.
Make sure every row in that column is filled by going to the first cell of that column, e.g. Cell C2, press Ctrl+Shift+DownArrow and see where it stops. If it stops at the last row of your dataset, then you need to highlight all the rows below, right click, and select Delete Rows.
If you just press the Delete key on your keyboard, Power BI will assume that the row exists but it is blank, so you have to use the Delete Rows in excel. I would highlight a bunch of rows to make sure I get all of them.
[Edit]
Also another way to do this is go to Power Query, go to the table with blank values, filter out the blank values.