Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
7 years ago
Solved

Removing rows query

Hello   I have a table with 4 columns and many rows. There's a numeric value in the first column and each value occupies either 3 or 4 rows. I need to remove all rows for those numeric values which...
Need Help
  • petrovnikitamai's avatar
    petrovnikitamai
    7 years ago

    tgjones43, probably yes. in this case u get column1 with 1 only

    .

    new column calculate count rows by column1.

    new column = countrows(filter(tableName;earlier([column1])=[column1]))

    u can use report level filter [new column] = 4 then u get

     

     

    other way u can filter your table in query editor

     

    let
        //your table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLOeYPDjbBcwygqs0gqs0Aqssgqo1hqs1gas1gas1QVFrAlEbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        //group by column1
        #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Cnt", each Table.RowCount(_), type number}, {"list", each [Column2], type text}}),
    //filter column2 by cnt=4
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Cnt] = 4)),
        //get our table
    #"Expanded {0}" = Table.ExpandListColumn(#"Filtered Rows", "list")
in
    #"Expanded {0}"

     

  • petrovnikitamai's avatar
    petrovnikitamai
    7 years ago

    tgjones43,fix query

    now u can get all columns from source table with filter by column2 with "D"

     

    notice, in this way u should write columnNames by arm on step with bold font

    i'm not sure, but mb exist the better way to write this query with better performance

    let
     //your table
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4hQgVorVgQg4gThIfOeYPKiSFLiYC5IaI6ghcAVGaIYYgQ0pghsDFjRGM8UE3RQTNFNMkExBFoMbEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
     //group by column1
     #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Max", each List.Max([Column2]), type number},{"records", each [[Column1],[Column2],[Column3],[Column4]], type text}}),
     //filter column2 by max="D"
     #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Max] = "D")),
     //delete usless columns
     #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"records"}),
     //get filtered records
     #"Expand{0}" = Table.ExpandTableColumn(#"Removed Other Columns","records",{"Column1","Column2","Column3","Column4"})
    in
     #"Expand{0}"

     

petrovnikitamai's avatar
petrovnikitamai
Resolver V
7 years ago

if u want to get column that will filtering your table

new column = countrows(filter(tableName;earlier([column1])=[column1]))

if u want to get a new table

new table = 
var x1 = addcolumns(tableName;"new column";countrows(filter(tableName;earlier([column1])=[column1])))
return filter(x1;[new column]=4)

 

  • tgjones43's avatar
    tgjones43
    Helper IV
    7 years ago

    Thanks, but I can't figure out how to make that work with my data, can you advise further? I also should have said that there are some rows of my table which contain the extra 4th row (with D in column 2) but they also need to be filtered out becuase they do not contain the other three rows that contain A, B and C in column 2 (see the row with 3 below).

     

    Will your solution still work in this case?

     

     

    • petrovnikitamai's avatar
      petrovnikitamai
      Resolver V
      7 years ago

      tgjones43, probably yes. in this case u get column1 with 1 only

      .

      new column calculate count rows by column1.

      new column = countrows(filter(tableName;earlier([column1])=[column1]))

      u can use report level filter [new column] = 4 then u get

       

       

      other way u can filter your table in query editor

       

      let
          //your table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLOeYPDjbBcwygqs0gqs0Aqssgqo1hqs1gas1gas1QVFrAlEbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
          //group by column1
          #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Cnt", each Table.RowCount(_), type number}, {"list", each [Column2], type text}}),
    //filter column2 by cnt=4
          #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Cnt] = 4)),
          //get our table
    #"Expanded {0}" = Table.ExpandListColumn(#"Filtered Rows", "list")
in
    #"Expanded {0}"

       

      • tgjones43's avatar
        tgjones43
        Helper IV
        7 years ago

        Thank you, that works great. But having done that, I realise my data is a little more complicated so I need a more advanced query. Refering to the table below, I need to retain every row for each numeric value in column 1 if one of the rows includes the value D in column 2. So in this case, all four rows containing 1 in column 1 are needed, as are the two rows that contain 3 in column 1. But the rows with 2 and 4 in column 1 need to be filtered out. I don't think the count function can be used because a numeric value with 2 rows may or may not contain D in column 2.

         

        Furthermore, I need the data in columns 3 and 4 to be retained too after the relevent rows have been filtered out.