Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,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}"
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}"
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.
@petrovnikitamai would you be able to help with this one? Would be greatly appreciated.
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"
Thanks @petrovnikitamai please could you write this in M language? I want to run this in the Power Query Editor.
@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,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,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,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}"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |