No Blanks in Power Query but then Blanks in Data Set?

[Thigs]

Hey all, 

Having a strange problem - 

I loaded my data set into PBI from Excel. There are no blank rows in the Excel file, I've triple checked it. 

In Power Query, there are no blanks in the data. 

However, once the data is loaded in, there are tons of blanks. Like, over 1 Million blank rows. This is only a problem because I'm trying to make the relationship One:Many but because of the blanks, it's saying I can only do Many:Many. 

Does anyone have a fix for this? I can't filter it out in Power Query because there aren't any blanks appearing in Power Query. 

[AlexisOlson · Accepted solution]

You definitely should be able to filter out blanks in Power Query. You might only be seeing a data preview in the query editor.

Try adding this step at the end of your query even if you don't see any blanks.

[Nickster424]

Thank you!!! I have been fighting with this for over a day and fortunately found this thread. 🎉

[Anonymous]

Thank you very much! It worked in my case, will list the details for other members as well:

1. During a merge operation, the Expended Columns had no empty rows in Power Query. 

2. After close & load to Excel, the Expended Columns did have empty rows

Your suggested solution, of removing blank rows before close & load, worked for me!

[Thigs]

That worked! Thank you so much!

[vanessafvg]

if you use the drop down arrow on any column in power query and filter out blanks it should filter it out, but because power query only returns the first 200 rows you probably can't see them.

The issue is you would have added, blank rows  in excel, probably white spaces  even if you cant see them.   But you should be able to remove them in power query but use the drop down arrow on the field in the display pane in power query to filter out the blanks, it will drop down a unique set of the values in that column, and you should see blanks there.