import of empty rows from Excel-file in SharePoint?

[Jolyon]

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!

[v-qiuyu-msft · Accepted solution]

Hi @Jolyon,

In your scenario, it seems the Excel was refreshed with some empty rows contained, while you didn't exclude those empty rows, so that the error throws out. Please open the report in Power BI Desktop and open Query Editor, then check the "Remove Blank Rows". Then republish the report to service.

Best Regards,
Qiuyun Yu