Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!

Need Help

8 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This was perfect, Thanks!

      I just needed to sort my rows appropriately prior.

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        2 years ago

        Anonymous 

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


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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