wrong import from Excel - empty rows

[Jolyon]

Hi everyone,
I would need your help.
I have made a report, importing the data from Excel-Sheet in SharePoint. The idea was, that I refresh this Excel once a week without changing the name or path, and then I can refresh automatically the Power BI report.
The report refreshed  correctly a couple of weeks,but today I  have  got a problem:

when refreshing the data Power BI took into consideration also empty Excel rows after the last Data row, i.e.

If my Excel looks like this:

Power BI takes both Data and empty rows,which I do not need(here is only an example, I have got actually minimum 500 null-rows):

That is why my Primary Key Table, which has the formula "Category ID = DISTINCT(Table1[Category])" can not refresh, and gives warning: "The table with Primary Keys can not contain NULL Values".

How can I "tell" Power BI to take only rows with data, and not empty rows?

Are there any ideas?

Thank you.

[v-ljerr-msft · Accepted solution]

Hi @Jolyon,

You will get empty rows when you delete some rows after you first import the data from Excel. For example, at first I import 10 rows from Excel, then I delele 3 rows, after refresh I will get 3 empty rows in Power BI.

In order to remove the empty rows, you can go to Query Editor, click Filter for your "Category" column, then select Remove Empty.

Regards

[Jolyon]

Thanks @v-ljerr-msft!

well, actually I didn't delete any rows in my first BI report, that's why it's a bit strange, that I have got empty rows from Excel now.

But anyway I just removed now empty rows, as you told. I will see, how the report will refresh next time;)

Thank you!