Add manual information into a query | Power Query | Excel Off The Grid

Introduction

In this tutorial, you will learn how to add manual information to a query in Power Query using Excel. This process is essential when raw data alone isn't sufficient for your analysis. By adding elements like categorization, commentary, or reference numbers, you can enhance your dataset and make it more useful for reporting or decision-making.

Step 1: Get Data and Unique Reference

• Import Your Data: Start by opening Excel and loading your data into Power Query.

• Identify Unique Reference: Ensure you have a column that contains unique identifiers for your records. This could be an ID number, a name, or any other field that uniquely identifies each row.

  Tip: If you don't have a unique reference already, consider creating one by combining multiple columns or using a sequential numbering system.

Step 2: Add Manual Information

• Create a Manual Information Table: In a separate sheet, create a new table that contains the manual information you wish to add. This could include:

  • Categories
  • Comments
  • Reference numbers

• Format Your Table: Ensure your manual information table has a clear structure with headers that match your unique reference column.

Step 3: Merge Manual Info with the Original Query

• Return to Power Query: Open the original query you want to enhance.

• Merge Queries:

  • Click on the "Home" tab and select "Merge Queries."
  • Choose your original query and the manual information table.
  • Select the unique reference column from both tables to perform the merge.

• Choose the Join Type: Opt for a left join to keep all records from your original query.

Common Pitfall: Ensure that the data types of the unique reference columns match in both tables to avoid errors during the merge.

Step 4: Refreshing the Data and Testing

• Close and Load: Once merged, close the Power Query editor and load the data back into Excel.
• Refresh Data: To ensure your manual information is always up-to-date, refresh the data by selecting the query and clicking on "Refresh."

Testing: Verify that the manual information appears as intended in your dataset. Check a few records to ensure the merge was successful.

Important Information

• Remember that any changes to the manual information table will require a refresh in Power Query to update the main data.
• Regularly check for discrepancies between your manual data and the original dataset.

Conclusion

In this tutorial, you learned how to seamlessly incorporate manual information into your Power Query datasets in Excel. This process not only enriches your data analysis but also ensures that your reports are comprehensive and informative. As a next step, consider exploring additional Power Query functionalities to automate your data processing further.