Search code examples
excelvbaexcel-formulapowerquery

Process automatization | adding a comments column to a queried table(X) in an excel sheet, then adding new data and replacing the X table

Context:

I have data from monthly files that contain invoices that are not paid. (Table X)

ID CLIENT NAME ... AMOUNT
1 A ABC ... 1000

Each file has a united structure, so I created a custom function that processes the files and stores the data in on big table.

The complexity starts here:

  1. Each month users manually go through the data and assign which invoices are paid, add comments etc.
  2. results in the data looking like this : (Table Y)
ID CLIENT NAME ... AMOUNT COMMENT PAID
1 A ABC ... 1000 PAID 1
2 A ABC ... 1000 PAID 1
3 B ABC ... 1000 PENDING 0

  1. I need to add the new moth's data without disturbing and/or affecting the "comments" assigned to the table in step 2 - keeping the structure and the comments in the right place.

  2. The problem is that the new month's files contain also the previous month's unpaid invoices (PAID = 0)

  3. I then need to remove duplicates while keeping the comments. (Table Z) (note: the paid invoices can stay there if it makes it easier) (Table Y)

ID CLIENT NAME ... AMOUNT COMMENT PAID
1 A ABC ... 1000 PAID 1
2 A ABC ... 1000 PAID 1
3 B ABC ... 1000 PENDING 0

...NEW DATA...(structure of the Table X )

| 3 | B | ABC | ... | 1000 | ...

  1. Then taking the Table Z and replacing Table Y with it.
  2. Repeat each month/time when new data is added.

I was thinking to do it all in Power Query, but I got nowhere.

A colleague suggested to do it with VBA

  1. Button: Update/Save Table Y
  2. Button: Create Table Z
  3. Button: substitute the Table Y with the Table Z

But so that it can be done over and over again.

Each file has a united structure, so I created a custom function that processes the files and stores the data in on big table.

Solution

  • Ok, I managed to create a self-referencing query. Thanks to @horseyride 's comment "See notes on self-referential powerquery tables to maintain comments upon refresh at exceleratorbi.com.au/self-referencing-tables-power-query ."

    Although the problem wasn't the same. The steps were similar quite straight forward. It was quite confusing to understand the actual mechanics behind it, but the links explain it very well.

    Visuals are found in the links: exceleratorbi.com.au/self-referencing-tables-power-query AND https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/?_ga=2.29192188.1106660051.1705039937-1062894945.1705039937

    1. You create and transform your raw data source(source can be a file, table etc.). TABLE X
    2. You add the necessary comment columns (leave them blank).
    3. Duplicate TABLE X.
    4. TABLE X now is TABLE Y.
    5. Append TABLE X to TABLE Y.
    6. Remove the added custom steps.
    7. Load the TABLE Y.
    8. Copy the table name.
    9. Go back and edit the source of TABLE Y to: = Excel.CurrentWorkbook(){[Name="TABLE Y"]}[Content]
    10. After the Step Append Query add remove duplicates while selecting all columns except the comments.
    11. Load TABLE Y and you can add comments.

    The only downside I found to this solution is that if you delete rows and refresh then the deleted invoices are going to appear on the bottom of the table because they are contained in TABLE X and therefore, they are appended on the bottom. But I don't see a logical reason Why would you want to do that.

    Later I found out that the invoices with the same INVOICE NUMBER in each file can have varying amounts that need to be paid because the invoices can be covered in portions (sort of like Payment plans). In my data the AMOUNT is the remainder of the original invoice. This complicates things a lot. I will create another post regarding this topic.