Remove all rows after specific row in Power Query

[vshala1]

Hi guys,

I have a very tricky question. 

Lets assume the following data table:

No    Name    Forname   ...

1       Morgan   John

2       Griffin      Peter

3       Johnson  John

4       ...              ...

5       ....             ...

Now I want to get all rows after "3 Johnson John". Problem being is that this might change in the next query dynamically. I have another table with just one entry which would be my row filter criteria with just:

3   Johnson    John

This I would like to use as a parameter for the filter process of the big table.

How can I search the whole table with my row filter criteria and output all values after this specific entry in power query?

I'd love to get some ideas from you guys.

Regards.

[Zubair_Muhammad · Accepted solution]

Hi @vshala1 

Please see if solution in attached file is useful

I named the table which contains rowfilter criteria as "RowFilter"

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNL0pPzAMyvPIz8pRidaKVjIAc96LMtLRMkHBAaklqEVjcGKqoOB9FuQmQE1WalAmkHIsz08BipkCOR2Ix2FygXGIm0IRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Name = _t, Forname = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Name", type text}, {"Forname", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
Rowstoskip = Table.SelectRows(#"Added Index",each [No]=RowFilter[No]{0} and [Name]=RowFilter[Name]{0} and[Forname]=RowFilter[Forname]{0} )[Index]{0},
Custom1 = Table.Skip(#"Added Index",Rowstoskip)
in
Custom1

[Zubair_Muhammad]

@vshala1 

Bascially,

1) we add index column to identify row number

2) get the index number for matching row

3) skip the rows upto that index number

[vshala1]

Thank you so much! I am nearly there I think. The only thing is that it says "Expression.Error: The name 'RowFilter' wasn't recognized. Make sure it's spelled correctly." although I named my table "RowFilter".

Any idea?

---

[vshala1]

Found it! Your solution works!