Pivote tabel changes recording

[bartrijnhart]

I am building a set of pivot tables form a power query with a link to cloud data base. that all works like I have designed it. however I have a college who would like to know when I or one of me fellows who work the data base directly have changed something. the question is it possible to have some kind marking in a pivot table to show the row has changed after a refresh of the data. light up a row with or cell with a collum or just a list of the main collum that has changed. If not there is a lot of work each month to see what has changed in the data base.

There is data on editor dates of the attributes. The whole database is loaded in as a JSON. From an ArcGIS geo cloud.

I hope some one has an idea for me how to fulfill the question that has risen within out organization.

[#2]

@bartrijnhart 

While directly marking changes within PivotTables themselves isn't possible, here are some approaches to help your colleague track data changes in your connected cloud database using Power Query (assuming Excel 2016 or later):

1. Version History within Power BI Service (if applicable):

• If you're using Power BI Service to share your data and reports, it provides a version history feature. This allows users to see changes made to datasets and revert to previous versions if needed.

2. Change Tracking within the Database:

• Many cloud databases offer built-in change tracking functionalities. You can utilize these features to log timestamps and user information whenever data is modified. This information can then be incorporated into your Power Query data model to display alongside the main data points.

3. Custom Calculated Column (Excel Power Query):

• Create a custom calculated column within Power Query using the TODAY function. This column will automatically update with the current date whenever the data is refreshed. You can then use conditional formatting in Excel to highlight rows where the "Date Modified" (from your database) and the "Last Refresh Date" (calculated column) differ, indicating a change.

Here's a step-by-step guide for the custom column approach:

a. Create a Calculated Column:

1. Go to the "Power Query Editor" (Data tab -> Get Data -> From Other Sources -> Blank Query).
2. Load your JSON data and transform it as needed.
3. Add a custom column by right-clicking on any column header and selecting "Add Column" -> "Custom Column."
4. In the formula bar, enter TODAY(). Name the new column "Last Refresh Date."

b. Apply Conditional Formatting in Excel:

1. Import your data model back into Excel.
2. Select the PivotTable.
3. Go to the "Design" tab and click "Conditional Formatting."
4. Choose "Highlight Cells Rules" -> "Use a formula to determine which cells to format."
5. Enter the formula: =([Last Refresh Date] <> [Date Modified]) (replace [Last Refresh Date] and [Date Modified] with your actual column names).
6. Choose the desired formatting (e.g., highlight) for cells that meet the criteria.

This approach provides a visual indication of changed rows within the PivotTable, but it requires a manual refresh each time to update the "Last Refresh Date."

4. Power Automate Flow (if applicable):

• If you're using Microsoft Flow, you can create a flow that triggers whenever changes occur in the database. This flow can then send an email notification or update a specific cell in your Excel sheet reflecting the change.

Choosing the Best Approach:

The best approach depends on your specific needs and tools available. Consider factors like:

• Cloud database features: Explore change tracking options within your cloud database.
• Reporting platform: Utilize version history features in Power BI Service (if applicable).
• Automation preference: Employ Power Automate Flow for automated notifications (if applicable).
• Simplicity: Implement the custom calculated column approach for a simple highlighting solution.

Remember to adapt these suggestions and consult official documentation for specific functionalities and implementation steps related to your chosen approach.