cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jolyon
Helper III
Helper III

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:

2.png

 

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):

 

1.png

 

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.

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

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.

10rows.PNG

3emptyrows.PNG

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

filter.PNG

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Microsoft
Microsoft

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.

10rows.PNG

3emptyrows.PNG

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

filter.PNG

 

Regards

View solution in original post

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!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors