Power Query Top N Filter

[Anonymous]

Hello, 

Pretty basic question here, as I am new to Power Query and Power Bi.

I have a single table coming in which has a [DataSetKey] identifier. The key comes in as a numeric value, such as 30509. I'd like to only load the top 10 DataSetKeys, so that I am not loading 100,000+ rows every time. Could someone throw me an example of how to do a Top N type filter in Power Query?

Thanks very much!

[Anonymous]

You can apply TopN option in the visual level filter.

Check this >>

https://community.powerbi.com/t5/Desktop/TopN/m-p/131646

[Icey]

Hi @Anonymous ,

If your data source is something like a SQL database, try this:

sql server - How to select only the top 1000 rows when importing from SQL to power bi? - Stack Overflow.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[mahoneypat]

Is there >1 row per datasetkey?  If so, you could first do a Group By on dataset keep with an Aggregation of Keep all rows.  You can then sort on dataset key descending and keep the first 10 rows.  You can then expand the column with the tables in it and load.

Regards,

Pat

[Fowmy]

@Anonymous 

You can add a new step as follows, replace PreviousStep with the relevant step name as per your file.

=  Table.LastN (PreviousStep, 10)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[ed-deisenstein]

This was perfect, Thanks!

I just needed to sort my rows appropriately prior.

[Fowmy]

@ed-deisenstein 

Glad it works for you!
Please accept my solution as the answer, it will be helpful for others who have similar issues.

[Anonymous]

Thanks for your quick response! I think Table.LastN will give just the number of rows to keep (or am I totally misreading that?). I don't know what the total number of rows that each DataSetKey will be, so I don't think this way will work.

I'll try to reword my question a bit. Each row has a identifier, refering to what data set each row belongs to. What would be a good way to only load the last 10 of the DataSetKey, without knowing how many rows are going to be queried? So what I'd want to do is bring in the top 10 DataSetKey values without knowing what the max value of the DataSetKey is or the number of rows that'd I'd expect, if all that makes sense.

[edhans]

@Anonymous if @mahoneypat 's answer isn't your solution, can you supply some sample data with expected results. You have to remember no one here knows anything about your tables. See links below for instructions.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.