Forum Discussion

martinqb's avatar
martinqb
New Member
3 years ago

Is this the correct use of Power Query?

I have dabbled quite a bit in Excel and VBA. I now try to learn about Power Query. I've come to realise that PowerQuery is mostly for data management/relations, whereas VBA should really only be used for cosmetic changes (as it takes a lot of processing power, correct me if I'm wrong). I'm starting to get a grasp of the concept, but I had a question:

Can PowerQuery replace index/match?

I have here two tables with the exact same information (using index/match). I didn't add anything new, other than different IDs. Would I be able to write a name in the secondary table and get the results from the primary table without the need for index/match?

I have attached screenshots of my excel file (I can't upload the actual file) to help me with this (I'm using it to learn more about power query).

I'm not interested (yet) in learning about power pivot though, as I see no need for pivot tables in my life.


Primary Table     Secondary Table (Index/Match by Name)

Need Help
Tutorial Requests

10 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, martinqb 

     

    Yes, Power Query can replace the need for Index/Match in many scenarios, and it can be a more efficient and user-friendly way to manage data and perform lookups. Power Query is designed specifically for data transformation and manipulation tasks, including merging, filtering, and transforming data from multiple sources.

    In your case, where you have two tables with the same information and want to perform lookups based on a common field (e.g., "Name"), Power Query can easily handle this. You can use Power Query to merge the two tables based on the "Name" column, effectively replicating the functionality of Index/Match.

    Here's a general outline of the steps you would take in Power Query to replace Index/Match:

    1. Load both tables into Power Query: Import both the "Primary Table" and the "Secondary Table" into Power Query.

    2. Merge queries: In Power Query, you can use the "Merge Queries" option to merge the two tables based on the "Name" column. This will combine the data from both tables into a single table.

    3. Expand the results: After merging the queries, you may need to expand the results column, so you can see the values from the "Primary Table" that match the "Name" in the "Secondary Table."

    4. Transform data as needed: If required, you can further transform the data, such as renaming columns or removing unnecessary columns.

    5. Load the results: Once the data is transformed, you can load the results back into Excel or create a new worksheet with the updated data.

    Power Query's approach is more streamlined and generally more efficient compared to using Index/Match formulas, especially when dealing with larger datasets. It also offers more flexibility in terms of data manipulation and data cleaning.

    Remember that Power Query doesn't change the underlying data; it only creates a new query with the merged and transformed data. So, if your data in the "Primary Table" changes, you can easily refresh the Power Query to update the results.

     

    GPT generated detailed reply. 

    To learn more about using Power Query for data manipulation, see

     

    https://learn.microsoft.com/en-us/power-query/

     

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      I can also use ChatGPT:

       

      ------------------------------------------

      Yes, you are correct in your understanding of the roles of Power Query and VBA in Excel.

      Power Query (also known as Get & Transform in Excel) is primarily used for data preparation, transformation, and data management tasks. It provides a powerful and user-friendly interface to clean, reshape, and combine data from multiple sources. Power Query is highly efficient in handling large datasets and can perform various data operations quickly and with ease.

      Regarding your question, yes, Power Query can replace the need for INDEX/MATCH in many cases. INDEX/MATCH is a popular Excel formula combination used to look up values in a table based on specific criteria. However, Power Query offers the "Merge" functionality, which can perform similar tasks more efficiently.

      In Power Query, you can merge tables based on one or more columns, just like using INDEX/MATCH with multiple criteria. The process is called "Table Merge" or "Join" in Power Query. By using Merge, you can avoid complex array formulas and improve the readability and maintainability of your data transformations.

      Here's a high-level example of how Power Query can replace INDEX/MATCH:

      Let's say you have two tables: Table1 and Table2, and you want to pull a value from Table2 into Table1 based on a common key (e.g., ID).

      Using INDEX/MATCH:

      In Excel, you would write a formula like this in a cell of Table1:

      =INDEX(Table2[DesiredColumn], MATCH([@ID], Table2[ID], 0))

      Using Power Query:

      1. Load both Table1 and Table2 into Power Query as separate queries.
      2. Merge Table1 with Table2 based on the "ID" column.
      3. Expand the desired column from the merged table.
      4. Load the final transformed data back to Excel.

        By following these steps, you can achieve the same result as INDEX/MATCH but in a more organized and efficient manner.

        Power Query's strength lies in handling complex data relationships, joining tables, and applying transformations, making it a powerful tool for data management tasks. However, it's essential to know that both INDEX/MATCH and Power Query have their places in Excel, and there might be scenarios where one is more suitable than the other based on the specific requirements of your task.

        ------------------------------------------

         

        PeteGPT