Count rows in query editor before applying filters

[Andy_T]

Hi all,

I have an Excel data source which I'm cleaning up using the query editor. I'd like to be able to report in Power BI how many rows there were before any filters were applied in the query editor (in order to tell the project manager how many rows of bad data there are..)

The only potential solution I've found is;

• Use 'Extract Previous' to split my query into two at the point of filtering
• Reference the query containing the pre-filter actions
• Add the Count Rows action to my reference query

Is there a more elegant solution that anybody is aware of?

Thanks.

[Greg_Deckler · Accepted solution]

I'm not sure what you would envision as elegant. You could create a custom column in the query editor that would utilize Table.RowCount function and put that step before any filters. You could also create a separate query and all it does is do this, if you want it in a separate table. Again, not sure what qualifies as elegant.

[Andy_T]

Hi, thanks for the reply, I'll take a look at doing a custom column. I was hoping that there would be something I could do with one query, rather than creating multiple queries to get to the result I'm looking for.

[MarcelBeug]

Most elegant would be to invite and tell the Project Manager at the bar over a nice glass of his favourite drink.