Removing rows query

[tgjones43]

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

 

[petrovnikitamai · Accepted solution]

@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]

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]

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 · Accepted solution]

@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]

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.

 

[tgjones43]

@petrovnikitamai  would you be able to help with this one? Would be greatly appreciated.

[petrovnikitamai]

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

try to add new column

u get column with max value in column2 by column1. in this way u can filter table if [new column] ="D"

[tgjones43]

Thanks @petrovnikitamai  please could you write this in M language? I want to run this in the Power Query Editor.

[petrovnikitamai · Accepted solution]

@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}"

[tgjones43]

Thank you @petrovnikitamai  you have been a great help.

[petrovnikitamai]

@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]

@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]

@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}"