Results 1 to 4 of 4

Thread: Is Power Query what I need?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is Power Query what I need?

    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.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,850
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Is Power Query what I need?

    Last edited by sandy666; Jun 27th, 2019 at 03:42 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is Power Query what I need?

    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).

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is Power Query what I need?

    +1 pq
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •