Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Power Query Top N Filter

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!

8 REPLIES 8
Anonymous
Not applicable

You can apply TopN option in the visual level filter.

Check this >>

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

Icey
Community Support
Community Support

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

This was perfect, Thanks!

I just needed to sort my rows appropriately prior.

@ed-deisenstein 

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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.

 

 

 

 

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors