Forum Discussion
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 only occupy 3 rows and keep all of those that occupy 4. So in the example below, the rows with 2 in the first column need to be filtered out. The value in the second column for the extra 4th row is the same for all numeric values.
Thanks
Tim
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,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}"
12 Replies
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)
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?
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}"
