Keep rows containing specified text strings

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
297
Office Version
  1. 365
Platform
  1. Windows
Apologies if this is a "newb" question, but Power Query is completely new to me and I've no idea how to go about this...

I have a text file which is data dump from our email client and contains almost a million rows, most of which I don't actually need for the report I'm creating. The data is a single column for each record and looks something like this (anonymised):
ACL check OK, connection accepted from peer <123.4.567.89>
Message from: [abcd.efg@companyone.com]
Message to: [joe.bloggs@companytwo.com]
..Scanning decoded file. HH01_18_08_00_08_27.jpg

<tbody>
</tbody>

What I need to do is to only pull through rows which contain the text strings "message to" or "message from"

I'm using Excel as part of the Microsoft Office Professional 2013 Suite and have downloaded and installed the Power Query AddIn from Microsoft.

I've created the basic query by linking to the text file, but it's bringing through all records rather than just those containing "message to" or "message from"

Any suggestions as to how to do this would be gratefully appreciated!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Panic over, I've found how to do it!

Edit Query, and THEN click the filter box in the top right and apply whatever parameters are required for your filtering. Microsoft could have made that a bit more obvious to find, although having now found it, it seems obvious with hindsight.

I had been trying to filter the data that had been brought through instead of filtering beforehand. I'd also played around with Parameters, but that wasn't what I needed in this instance.

I've managed to go from 798,000 records in total to the 168,000 that I actually need. I can then further manipulate those records with VBA merging them with data from other sources to generate my final report. I may ultimately be able to do everythign in Power Query, but this will work for now until I've learned more about the feature.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top