How to only pull in certain number of rows from folder of Excel spreadsheets?

[RachelFoulg]

Hi,

This is my first forum post! I'd love some help with a difficulty I've been having please.

Every day I receive a spreadsheet via email that contains a list of our products, their chart rankings, and their stock status. The number of data rows varies each day but can have anywhere between 26,000 - 76,000 rows of data. I currently save each day's Excel file in a folder and then use PowerBI to connect to the folder, add the date using applied steps in the PowerQuery editor and then combine/load so that I can analyse the data over the long-term.

The problem is, this is a huge amount of data that we don't necessarily need. We only really want to analyse our top selling/ranking products, so I'd ideally like to only import the top 10,000 (or even 5,000) rows of data from each spreadsheet every day. Does anybody know how I can do this easily? Or have a better solution?

Thank you very much if so,

Rachel

[v-henryk-mstf]

Hi @RachelFoulg ,

According to the error message, you cannot merge binary files directly.
If these files are located in the same folder, you can refer to this document to merge the binaries in the Power bi desktop.

Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RachelFoulg]

When I tried to use the FilterRows parameter I get the following error:

Expression.Error: We cannot convert the value 5000 to type Binary.
Details:
Value=5000
Type=[Type]

[amitchandak]

@RachelFoulg , refer the approach show here and only use filter part

https://blog.crossjoin.co.uk/2020/05/24/limit-the-amount-of-data-you-work-with-in-power-bi-desktop-u...

[RachelFoulg]

Thank you for this, unfortunately I can't get it to work for me. I tried from a new blank document, I click Get Data, navigate to the folder I want, click transform and load as each spreadsheet contains two blank columns at the end of a table so these need to be deleted first before Power BI will load them. My queries look like this afterwards: