Forum Discussion

supergenius123's avatar
supergenius123
Frequent Visitor
1 year ago
Solved

Remove Rows if it contains text from any other another row

In Power Query, I want to delete/remove all rows that contain the value of another row as a prefix. For example I want to transform the table below so that it contains only 2 rows, A and B

 

 

Need Help
  • Gabry's avatar
    Gabry
    1 year ago

    Hello supergenius123 ,

    this is your code, to add in PQ

        #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Rinominate colonne", "IsPrefix", (currentRow) =>
        let
            AllValues = List.Distinct(#"Rinominate colonne"[Value]),
            OtherValues = List.RemoveItems(AllValues, {currentRow[Value]}),
            IsPrefix = List.AnyTrue(List.Transform(OtherValues, (x) => Text.StartsWith(currentRow[Value], x)))
        in
            IsPrefix
    ),
    #"Filtrate righe" = Table.SelectRows(#"Aggiunta colonna personalizzata", each [IsPrefix] = false),
    #"Rimosse colonne" = Table.RemoveColumns(#"Filtrate righe", {"IsPrefix"})
in
    #"Rimosse colonne"

     

    Let me know 😉

2 Replies

  • Gabry's avatar
    Gabry
    Super User
    1 year ago

    Hello supergenius123 ,

    this is your code, to add in PQ

        #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Rinominate colonne", "IsPrefix", (currentRow) =>
        let
            AllValues = List.Distinct(#"Rinominate colonne"[Value]),
            OtherValues = List.RemoveItems(AllValues, {currentRow[Value]}),
            IsPrefix = List.AnyTrue(List.Transform(OtherValues, (x) => Text.StartsWith(currentRow[Value], x)))
        in
            IsPrefix
    ),
    #"Filtrate righe" = Table.SelectRows(#"Aggiunta colonna personalizzata", each [IsPrefix] = false),
    #"Rimosse colonne" = Table.RemoveColumns(#"Filtrate righe", {"IsPrefix"})
in
    #"Rimosse colonne"

     

    Let me know 😉