Forum Discussion
Solved
Remove all rows after specific row in Power Query
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 ...
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
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
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
