cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

if cell has a number delete row

I have a column that cells either have text or have numbers. If the cell is a number, I want the row deleted. Any recommendations?

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Syndicate_Admin 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWSgSTRmAyCUwag8lkpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type number}}),
    #"Kept Errors" = Table.SelectRowsWithErrors(#"Changed Type", {"Column1 - Copy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Kept Errors",{"Column1 - Copy"})
in
    #"Removed Columns"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

NewStep=Table.SelectRows(PreviousStepName,each not [ColumnName] is number)

AlB
Super User
Super User

Hi @Syndicate_Admin 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWSgSTRmAyCUwag8lkpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type number}}),
    #"Kept Errors" = Table.SelectRowsWithErrors(#"Changed Type", {"Column1 - Copy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Kept Errors",{"Column1 - Copy"})
in
    #"Removed Columns"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors