Forum Discussion
wrong import from Excel - empty rows
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.
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
2 Replies
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
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!
