Is Power Query what I need?

REJohnson

New Member
Joined
Jun 27, 2019
Messages
1
I perform a monthly analysis using source data that is over 400K lines of data. Each line contains information like transaction total, commission amount, and commission percentage. Currently, I have to manually remove any lines that fall within certain criteria. For example, if a line has a transaction total of 75 but the commission amount is 0, we determine that that line needs to be removed from the data set.

A manager suggested we use Power Query, which I hadn’t even heard of. Would Power Query be the best method to use to identify ‘bad’ data from such a large data set? Currently we’re simply using the filter function to filter columns.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
In the example that you give, it would be simple to write some VBA that adds a helper column with a formula that tests whether that exception criteria is met or not, and then delete those rows that meet that exception criteria.

In Power Query you would do a similar thing, add a column that tests for the exception criteria and filter out the rows that do meet them.

But … in my view, in VBA the rules are not necessarily that simple to maintain, for instance adding a formula to an Excel column is not hard, but it is not simple either. In Power Query, the rules are a lot simpler to maintain, they are a step in the expression. Most of the work is done in the UI not in code, you would still have to code the criteria, probably a simple If test. but much more straightforward. Adding new rules is also more straight-forward in Power Query, it is a lot harder to make a mess of the query than it is to make a mess of your VBA.

You only mention one exception, we don't know all of the things you do manually, but it looks a perfect job for Power Query to me, and would be far simpler than VBA (and I am someone who has been coding with VBA for over 20 years, I still love it, but I love Power QUery also).
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

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