Power Query - Select certain rows

[mandar5118]

Hi, I have certain rows which I want to filter the table. So I only need the first 12 rows of every attribute (without changing the current sort order)

How can I filter the table? I know I can manually do that by duplicating queries and then filter the data, but that will be very time consuming as there are 50+ columns. Also I am not sure if any new Attribute might get added/removed. Thanks

[mandar5118 · Accepted solution]

No, there are instances where I need debit and credit both

[mandar5118]

Also if there is a way I can split the queries based on the attribute, that would also help. I can then select keep top 12 rows and then append all the queries

[AlienSx]

Hi, @mandar5118 

filtered = 
        Table.Combine(
            Table.Group(
                your_table, 
                "Attribute", 
                {{"rows", each Table.FirstN(_, 12)}}
            )[rows]
        )

[mandar5118]

It does give me 12 rows per attribute but the original sort order is disturbed

[AlienSx]

never saw Table.Group or Table.Combine are shuffling data. Anyway, add index column before Table.Group and use this code. 

idx = Table.AddIndexColumn(your_table, "idx", 1, 1),
filtered = 
     Table.Combine(
         Table.Group(
             idx, 
             "Attribute", 
             {{"rows", each Table.FirstN( Table.Sort (_, {"idx"}), 12)}}
         )[rows]
     )

[jennratten · Accepted solution]

In order to retain the sort order, you will need to add an index column and then buffer the table before grouping, like this:

AddIndex = Table.AddIndexColumn ( your_table, , "Index", 1, 1, Int64.Type),
Buffered = Table.Buffer ( AddIndex ),
filtered = 
        Table.Combine(
            Table.Group(
                Buffered, 
                "Attribute", 
                {{"rows", each Table.FirstN(_, 12)}}
            )[rows]
        )

[AlienSx]

Hi, @jennratten it seems to me that Table.Buffer alone should work.

[jennratten]

Hi @AlienSx , sort of.  In the past, I have seen that Table.Buffer alone will work with small tables in which the buffer does not require paging but when paging occurs the rows are reordered by buffer's internal process.  However, I am not abe to find my past notes on this or other posts, so I may not be remembering that clearly.  However, the second purpose is for the model.  Once the table is loaded to the model and placed in a visual, it is nice to be able to specify the sort and the index will enable that functionality.  Based on how the visual is actually needing to appear, there could be other types of indexing needed.

[jennratten]

Hello - it looks like you are trying to retain the rows where Column 1 is Aug 2022 - Jul 2023 and column 2 = credit.  If that is correct, I recommend filtering those two columns.  That should leave you with the expected result without changing the sort order.

[mandar5118 · Accepted solution]

No, there are instances where I need debit and credit both