powerquery m language - how to select all rows until value

1
votes

Very similar question to this one but using Power Query/M

Given the following (Power Query Excel import) ... 

    A       B
1   Item    Amount
2   Item1   1
3   Item2   4
4   Grand   5

How do you select all the rows up until (excluding) the fourth row with Grand? (and excluding all rows after)

I have created a new column like this:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "match_check", each Text.Contains([A],"Grand"))

and it indicates correctly the "Grand" line, but what is really needed are all the lines ahead of it (and none of the lines after it).

1
powerquerym

1 Answers

2
votes

That's easy! :))

Continuing your code:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "match_check", each Text.Contains([A],"Grand")), //Your line

AddIndex = Table.AddIndexColumn(#"Added Custom", 1, 1),
SelectGrandTotals = Table.SelectRows(AddIndex, each [match_check] = true), //select matched rows with grand totals
MinIndex = List.Min(SelectGrandTotals[Index]), //select first totals row index (if there are several such rows)
FilterTable = Table.SelectRows(AddIndex, each [Index] < MinIndex) //get all rows before