How to add manual information to a query (commentary & references)

There are many scenarios where raw data itself isn’t enough to complete a task efficiently. For example, we may need to add manual information, such as categorization, commentary, or reference numbers.

In this post, we look at this process and understand how to add manual information to a query.

• Key principles
• Example
• Process
  • Step #1: Connect and Combine the CSV files
  • Step #2: Add manual information column
  • Step #3: Merge the query Table back into the first query
  • Step #4 Refreshing the query

  Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

  File name: 0186 Add manual info to a query.zip

  Watch the video

  

  Key principles

  If we want to load data from a source using Power Query and add manual information, there must be a unique reference for each row in the Table.

  Since the positions of rows may change, we need a unique reference to ensure the manual information is merged with the correct row of the data.

  Example

  In many scenarios, the input will already include a unique reference. In this case, the process is reasonably straightforward.

  Therefore, I wanted to work through an example where we need to create a unique reference as part of the process itself.

  This is the example we are working through:

  • CSV bank statements are saved in a folder. Power Query connects to the folder and combines the CSV files
  • The combined bank statement is loaded into an Excel Table.
  • We add additional information to the Table (it could be categorization, commentary, reference numbers, etc.)
  • When we refresh the query to add more CSV files, the additional information must remain with the correct data row.

  Since bank statements, in my experience, do not have unique reference numbers for each row, we must create the reference ourselves.

  Here is a screenshot from one of the CSV files.

  

  Note: In the example, the dates in the CSV files are UK dates (dd/mm/yyyy format). Even if your region uses an alternative date format, it should not prevent you from completing the example.

  Process

  OK, let’s build out the solution.

  Step #1: Connect and Combine the CSV files

  The actions for Step 1 are covered extensively in other posts (combine files in a folder using Power Query), so we won’t go into tremendous detail here.

  Let’s start by getting the data from the folder.

  • In Excel, click Data > Get Data > From File > From Folder
  • In the navigation window, find the folder location file where the CSV files are saved, then click Open.
  • Power Query displays all the files in the folder in a preview window; click Transform Data.
  • At the top of the Content column, click the Combine icon (double down arrow).
  • In the Combine Files dialog box, a preview of the first CSV file appears, click OK.
  • Power Query adds various helper queries.

  At this point, the Preview Window should look like this:

  

  Using the Source.Name (i.e. the name of the file) and a row number from each CSV we can create a unique reference.

  Select the Transform Sample File query.

  From the ribbon, click Add Column > Index Column. This gives us a row number within each CSV file.

  

  Select the query with all the files combined (Files in our example)

  Select the Index column, then hold Ctrl and select the Source.Name column.

  From the ribbon, click Transform > Merge Columns

  In the Merge Columns dialog box, select a Separator (I’ve chosen colon) and provide a New Column Name (I’ve chosen Unique Ref). Click OK.

  

  Now we can close and load the query as a Table in Excel by clicking Home > Close & Load (assuming you have the default setting applied).

  Excel displays a Table with the bank transactions along with a Unique Ref column.

  

  That completes Step #1.

  Step #2: Add manual information column

  Now let’s add the column for the manual information. In the example, I’m adding a commentary column.

  In the cells to the right of the query Table add a new column header called Commentary and add a few lines of example text.

  

  That’s it for Step #2.

  Step #3: Merge the query Table back into the first query

  We want to load the Table with the additional column into Power Query.

  Select a cell in the Table and click Data > From Table/Range

  To keep things clear. Let’s call this new query Manual Info

  Next, we want to merge the Commentary column from the Manual Info query into the first query, using the Unique Ref we created earlier.

  Select the first query, then from the Ribbon, click Home > Merge Queries.

  In the Merge Queries dialog box enter the following:

  

  • Top query: The first query, select the Unique Ref column
  • Bottom query: Manual Info query, select the Unique Ref column
  • Join Kind: Left Outer
  • Click OK

  A Manual Info column is added to the first query.

  Expand the new Manual Info column, but include only the Commentary column.

  

  We don’t need to load the Manual Info query into Excel. So, click Home > Close & Load (drop-down) > Close & Load To…

  In the Import Data dialog box, select Only Create Connection, then click OK

  

  The Table in Excel now show Commentary and Commentary2 columns.

  

  Delete the Commentary2 column, this is the manual column we created earlier, which is no longer required.

  Step #4 Refreshing the query

  We can now add more commentary, we can also add new files to the folder. When we click Data > Refresh All, the manual information stays with the correct data row.

  Important considerations

  Using this technique there are many things that might catch us out:

  • If anybody changes the file name of an existing CSV file in the folder, it will result in the commentary being lost.
  • If using a bank statement downloads to replace a previous statement, the row order must be identical to the original file. In Power Query, you may need to reverse the transaction order, so the oldest transactions are at the top.
  • The manual information exists in a temporary state, so make sure to keep suitable backups. If somebody does something dumb, you could lose it.

  Using this technique in other scenarios

  There are many other scenarios where this technique could be useful:

  • Writing commentary to appear in a report.
  • Adding an adjustment column to correct historical information without changing the original.
  • Add reference numbers to enable automatic reconciliations using the method in this post: Automate Reconciliations with Power Query.

  Conclusion

  In this post, we have seen how to add manual information to our data using Power Query and Tables.

  The key elements for success are:

  • Using, or creating a unique reference number.
  • Where the process creates unique references, ensure those references do not change.

  Related Posts:

  • Power Query: How to Combine files based on a list
  • Automate Reconciliations with Power Query (amazing time saver)
  • Power Query: Lookup value in another table with merge

  Discover how you can automate your work with our Excel courses and tools.

  

  Excel Academy
  The complete program for saving time by automating Excel.

  

  Excel Automation Secrets
  Discover the 7-step framework for automating Excel.

  

  Office Scripts: Automate Excel Everywhere
  Start using Office Scripts and Power Automate to automate Excel in new ways.