Deleting rows by specific set of values

[itspossible]

Hi! I have a somewhat straightforward issue I'm stuck with.

I have several thousands of rows of data from a marketing channel, and one of the 14 columns is called value. While this is supposed to be just numbers, the API seems to be spitting out some text/chars. So I would simply like to delete all rows that contain text/char in the column "value".

The added complexity here is that all the text values different, and new entries are added each time new data is loaded (so manually filtering using the dropdown is not an option and I need a formula). That beign said, all text/char values start with "{".

So basically I'd like the formula to be able to delete all rows where the column "value" contains any data which begins with a "{" i.e. a wildcard of sorts.

Any suggestions please?

[HotChilli · Accepted solution]

The fact that all the rows you want to remove have the value starting with '{' is the key to solving this.

Go to 'edit queries' -> Click on the dropdown for the column value. Choose 'Text Filters' -> 'Does not begin with' -> Enter the '{' character.

This will generate the 'M' language step so that your dataset keeps rows that value does not begin with '{'

[kkane]

Hi sorry,

I don't know if I'm being stupid but I can't find the option to 'edit queries'

[itspossible]

Great - would that apply proactively moving forward? i.e. when more instances of values starting with "{" get added with each refresh?

[PattemManohar]

Yes, but Don't forget to click on "Refresh" button to see the latest data...

[HotChilli]

yes, the 'Applied Steps' in Power Query Editor- Query Settings are a menu for your data import to follow. Every time. 

[PattemManohar]

Give a try with following:

Under Data Section, "Edit Queries" add a conditional column to have values that doesn't begin with "{" else NULL value. Then use Remove Rows option to delete rows that contain NULL Value in that conditional column. Hope that will resolve your issue..