topic Power Query - Select certain rows in Power Query

Power Query - Select certain rows

mandar5118 — Tue, 21 Mar 2023 09:18:51 GMT

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

Re: Power Query - Select certain rows

jennratten — Tue, 21 Mar 2023 09:40:09 GMT

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.

Re: Power Query - Select certain rows

AlienSx — Tue, 21 Mar 2023 09:40:52 GMT

Hi, mandar5118

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

Re: Power Query - Select certain rows

mandar5118 — Tue, 21 Mar 2023 10:13:05 GMT

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

Re: Power Query - Select certain rows

mandar5118 — Tue, 21 Mar 2023 10:14:39 GMT

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

Re: Power Query - Select certain rows

mandar5118 — Tue, 21 Mar 2023 10:15:17 GMT

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

Re: Power Query - Select certain rows

jennratten — Tue, 21 Mar 2023 10:42:36 GMT

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] )

Re: Power Query - Select certain rows

AlienSx — Tue, 21 Mar 2023 10:43:39 GMT

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] )

Re: Power Query - Select certain rows

AlienSx — Tue, 21 Mar 2023 11:13:25 GMT

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

Re: Power Query - Select certain rows

jennratten — Tue, 21 Mar 2023 12:48:40 GMT

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.