Limiting Rows in PowerBI

[PBandI]

I am using power query to query from a spreadsheet managed on a sharepoint site for my company. Currently when I import this data I am importing 1,048,575 rows (I believe this to be the limit of excel). The sheet that I am importing only has about 600 rows in use although this changes dynamically and grows every day (not drastically). Is there a way to only query rows that contain data? I have tried multiple different methods but none seem to give me exactly what I'm looking for. I tried using the Remove Blank Rows function but that gave me an error. I then found a way to limit the rows I import using parameters and adjusting some of the logic there. This seems to work and Power BI will only import the number of rows that I have specifically set, but is there a way to do this dynamically? I'm trying to see if there is a way that Power BI can parse my sheet and only import rows that contain data? I'm also wondering if maybe I just limit the worksheet size if that would solve my issue, but it doesn't seem to work. Not sure why PowerBI is pulling in over a million rows when the majority of them have no data, any help on this topic would be greatly appreciated!

[Anonymous]

Hi @PBandI ,

Thanks for the reply from NaveenGandhi / christinepayton .

Regarding your question about dynamically setting the import to only include rows that contain data, I don't see any functionality in Power BI that automatically adjusts the row limit based on the presence of data.

There are a few ways I can ensure that only necessary rows are imported:

1. Open the Power Query Editor.

Select the first column, then hold down the Shift key while selecting the last column. This should select all columns.

Go to the Home tab and click Remove Rows -> Remove Blank Rows.

2. Sometimes, cells may appear to be empty but contain invisible characters.

Use the Trim function in Power Query to remove any leading or trailing spaces that may cause Power BI to treat the row as containing data.

Feel free to contact me if you have any further questions.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[christinepayton]

Are entire rows blank, or just certain fields? The Excel connections work much better if you insert a table in Excel around the data from the insert menu. Then when you connect to the file in Power BI, you check the box next to the table name you want to import, and it will only pull the content of the table, not the whole sheet. This is assuming its picking up blank rows above or below your table right now, though. 

[PBandI]

@christinepayton thank you for the advice! If I was to do this and insert a table in the excel file around the already existing data, would I need to redo my Power BI connection and any relationships thereafter? 

[christinepayton]

It'd change your query a bit, but you can use the same query table so that you don't have to redo things. What I usually do is create a separate query with the "new" technique, then go to the advanced editor and copy/paste the connection chunk from the top few rows and replace the connection chunk in the original query. When you do that, you have to update whatever the next step is below the connection to reference the last step name that you pasted in, because it will be slightly different. You can make a copy of the file beforehand or discard the query changes if something gets screwed up. 

[NaveenGandhi]

Hi  

Did you try copying the data to new excel and checked if the issue still presists? 

[PBandI]

I have not tried this yet, the sheet is a live excel hosted on our sharepoint site so I would like to keep the sheet consistent so everyone who currently uses it knows where to find it