Theory problem: connecting comment column to database-connected pivot table

[efire_]

Hello! Here's a quandary I've been mulling over at work. I need to create a .xls that will:

• Connect to the org financial database, which is not editable and cannot be added to. Not a problem, as I have database access all set up.
• Display month-by-month revenue in a pivot table, with customer accounts as rows, and months as columns. Also easy, this took only a few minutes to do.
• Have an extra editable column directly next to the pivot table, so management can add comments on revenue trends for each customer account. Done, but...

Here's the issue: this pivot table must be refreshed weekly. As the table is sorted by total revenue, the customer accounts reorder every time I refresh. That means a comment written in row 20, for the customer previously in row 20, now appears like it's a comment for the new customer sorted into row 20.

For reasons outside my control, I can't fix this from a database level. Any way to allow users to both add comments AND keep those comments tied to each customer account, regardless of customer sort order? I could use PowerBI if needed - but I doubt that would solve the issue better than Excel, in this case.

Bonus problem: if possible, it would be amazing if I could automatically save the pivot table data each week, then compare it to the last few weeks' data - thus showing trends over time. Is that possible - to have Excel "save" the old pivot table data before the refresh, and categorize that old data somewhere with a date dimension added? Hopefully this all makes sense - I would show pictures, but confidentiality prevents that. Let me know if building an example file might help, or if this is explanation enough!

Thank you very much in advance for any ideas!

 

[Legacy 456155]

Any way to allow users to both add comments AND keep those comments tied to each customer account, regardless of customer sort order?

Is there any column or combination of columns in your pivot table that uniquely identifies each row? If so, then yes.

it would be amazing if I could automatically save the pivot table data each week

Of course you can. My further input here depends on the answer to the first question.

Have a nice weekend!

 

[efire_]

Thank you for the quick reply! Yes - the Customer Account Names are all unique per row. Example table below - unique customers in column 1, then each week of revenue in the rest of the columns.

Customer Account Name	9/25/20 Revenue	10/2/20 Revenue	10/7/20 November Revenue
Customer X	$100	$110	$120
Customer Y	$50	$30	0
Customer Z	$200	$220	$100

 

[Legacy 456155]

Let me know if building an example file might help

Yes it would help. If you don't have file-sharing available, let me know.

 

[Fraserj1979]

Hi, was there ever a resolution to this ? I am doing something very similar where I refresh pivot data daily but I want a comment to stay with the data unless it drops off the pivot table

Thanks all

 

[efire_]

Hi, was there ever a resolution to this ? I am doing something very similar where I refresh pivot data daily but I want a comment to stay with the data unless it drops off the pivot table

Thanks all

I actually just recently found what MIGHT solve the issue: self-referencing queries. Still don't understand it fully, but youtube search "power query self referencing" and you'll get a few hits.