Forum Discussion

vshala1's avatar
vshala1
New Member
7 years ago
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       ...              ...

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.

Need Help
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        vshala1
        New Member
        7 years ago

        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?