import of empty rows from Excel-file in SharePoint?

Hi everyone,

I urgently need your help.

I made a report, importing the data from Excel-file in SharePoint(i.d.giving in Power BI the path to this file). 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  rows after the last Data row, i.e.

if my Excel looks like this:

then 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?

As the report thought to be automatically refreshed every week, I try to find a solution like "exception catching", so that i should not manually delete all the rows or rebuild the report..

Probably there is also better way to integrate SharePoint file to Power BI?

Thank you!