Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SJHALANI
Helper I
Helper I

Remove rows based on condition

Hi,

I have a table where I want to remove rows based on a given value in a column. For e.g., from the below table, I want to retain only the rows where Col D says "TBD". Hence, would want to retain rows 3 & 5 only and delete the rest. How can this be done using Power query? Any help would be appreciated. Thanks! 

Col ACol BCol CCol D
aadatadata123
bbdatadata321
ccdatadataTBD
dddatadata456
eedatadataTBD
1 REPLY 1
KeyurPatel14
Responsive Resident
Responsive Resident

Hi @SJHALANI ,
Hope you are doing well.
Please paste the below M Code in your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PUXBU0gHTTlDaGUq7KMXqRCslJgK5KYklSJShkTFYKikJQ8rYyBAslZyMIRXiBDEwJQVDysTUDCyVmopdVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col A", type text}, {"Col B", type text}, {"Col C", type text}, {"Col D", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Text.Contains([Col D], "TBD"))
in
#"Filtered Rows"

If this helps then please give it a kudos and mark it as a solution.
Thank you.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors