Forum Discussion

Experteam's avatar
Experteam
Regular Visitor
7 years ago
Solved

Filter row with Power query m

Hello, I would like to filter the rows of a column that starts with the first two characters "to" and hides them from my table. I do not master the conditions under power query M.

 

Example: If my line in the "case" column begins with "to" then hide or delete it if possible. Every month the merge of the new files adds this kind of line that bothers me.

 

Example of line i want delete in the same column :

 

"to 01 april of 31 april"

"to 01 march of 31 march" 

 

thank you

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Try this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslXSEnNSS1JVcgvUsjLL4HylGJ1opVAXLg8WASJFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Column1], 2), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted First Characters", each ([First Characters] = "no"))
in
    #"Filtered Rows"

    Basically, create a new column that extracts the first 2 characters. Filter out "to"

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Try this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslXSEnNSS1JVcgvUsjLL4HylGJ1opVAXLg8WASJFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Column1], 2), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted First Characters", each ([First Characters] = "no"))
in
    #"Filtered Rows"

    Basically, create a new column that extracts the first 2 characters. Filter out "to"

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Experteam,

     

    Since you are not familiar with M, please also refer to the snapshot below, which shows Greg's solution.

    Filter-row-with-Power-query-m

     

    Best Regards,
    Dale

    • Experteam's avatar
      Experteam
      Regular Visitor
      7 years ago

      THX guys

       

      At the moment I used the filter at the report level