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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,101,905
Messages
5,483,653
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top