Forum Discussion
Tracking data changes
I am trying to create a visual as per below where I can track changes in my data on a weekly basis by selecting 2 reports (as shown in the example below Week1 and Week3)
Let's take the below table as an example. Let's assume every week I run a flow to automatically grab a snapshot of the current data in my Excel file and store these files into a SharePoint folder which is connected to my PBI file.
Example -
The week 1 table is showing my data at a given snapshot date.
Week 1:
| Project Name | Project ID | Programme ID | Forecast | Status |
| A | ProjectID 1 | ProgrammeID 1 | £500,000.00 | Status A |
| B | ProjectID 2 | ProgrammeID 2 | £500,000.00 | Status B |
| C | ProjectID 3 | ProgrammeID 3 | £500,000.00 | Status C |
| D | ProjectID 4 | ProgrammeID 4 | £500,000.00 | Statuc D |
One week later my power flow takes a snapshot of my Excel file and stores Week 2 into my SharePoint folder
Week 2: (changes from week 1 and week 2 in 'Forecast' and 'Status' columns.
| Project Name | Project ID | Programme ID | Forecast | Status |
| A | ProjectID 1 | ProgrammeID 1 | £500,000.00 | Status A |
| B | ProjectID 2 | ProgrammeID 2 | £640,000.00 | Status H |
| C | ProjectID 3 | ProgrammeID 3 | £700,000.00 | Status G |
| D | ProjectID 4 | ProgrammeID 4 | £500,000.00 | Statuc O |
After several weeks I would have multiple snapshots of this table with data changing each week. I want to create a visual which allows me to see which field have been changed. Something like the below image:
If i select week 1 and week 3 in my filters, the column+row which have changes in them would be highlighted.
I hope this is clarifies? If not please let me know!
Ok, now I see that the tables are not the same. Actually - at all. This will be harder to approach, but can be done.
1. I've created a new folder on my computer: "D:\PowerBI\Tracking changes in files\source" that contains the files from you (you can change it later to Sharepoint folder).
2. I've prepared a parameter for you that stores folder location so you can see it later:
3. I've created a "New source" from folder and set up a path to previously created parameter
4. As you can see it detects all the files in this folder, so make sure that this folder contains only a desired files. Yes, you can filter unnesessery files eg. when extenstion doesn't match, but I want to keep it simple.
5. For this example I will show you how to import Overview tables from folders, but it needed to be done for each sheet that you want to track. And also - it can be done by function so it will detect all the Sheets in the file and track them all but to see if that was what you wanted lets do it manualy only 2 of them: Overview and Milestone
6. I've renamed folders, parameters and functions here, because it will be easier to understand whats goin on.
7. In the "OverviewTransform SampleFile" table will add an index column so we can track changes for each row and do not lose this information.
8. In the "OverviewTransform SampleFile" table Select Index column and then Unpivot all other columns
9. In the "OverviewTransform SampleFile" table se should see something like this:
10. In Overview table we should see something like this:
So we have filename, index (row number in that file), columnname and value of that column.
11. Let's add a new custom column with a name of this table (Overview).
12. Done. No we need to repeat that to Milestone table.
- Import from folder based on folderPath parameter
- this time select Milestone sheet
- rename queries and parameters
- add index
- unpixot rest of the columns
- add custom column
13. We should see something like this:
14. Now we want to combine them into one table, so we are selecting Home > Appent Queries > As new
15. Select option that you want 3 or more tables and select tables to append
16. Right now we have such table
Source.Name: file names that information is coming from
SourceTable.Name: a table name from that file
Index: row index
ColumnName: business column name
Value: value of that column and index in that file
17. On the Overview and Milestone tables uncheck the "Unable load" option so we won't have duplicated information.
This post is getting too long so let me finish here and create a new answer how to viz it.
Step 1: Create 2 tables containing possible filter to files. Previously it Was 'Sample'Snapshot. Right now it's FilesTracker[Source.Name]
FileFilter_1 = GROUPBY(FilesTracker;FilesTracker[Source.Name])FileFilter_2 = GROUPBY(FilesTracker;FilesTracker[Source.Name])Step 1.1. (optional): I always want to write DAX in dedicated table so I've created a new calculated table DAX that holds everything.DAX = ROW("DAX";"DAX")
Step 2: Create a value measure for File1Value from file 1 =var selectedFile = VALUES(FileFilter_1[Source.Name])return CALCULATE(SELECTEDVALUE(FilesTracker[Value]);FILTER('FilesTracker';FilesTracker[Source.Name] in selectedFile))Step 3: Create a value measure for File2Value from file 2 =var selectedFile = VALUES(FileFilter_2[Source.Name])return CALCULATE(SELECTEDVALUE(FilesTracker[Value]);FILTER('FilesTracker';FilesTracker[Source.Name] in selectedFile))Step 4: Create a measure that defines if it's a change or notChanges detector = IF([Value from file 1]<>[Value from file 2];1;0)Step 5. Put up a filter on table to see only rows that has some changesStep 6. Put all filters on the page and viz it as you want 🙂
Final effect:
21 Replies
I am not sure if I've understood you correctly, but... this is the data that I've used:
Step 1: Create 2 tables containing possible filter selections
Snapshot 1 = DISTINCT('Sample'[Snapshot])
Snapshot 2 = DISTINCT('Sample'[Snapshot])
Step 2: Create a Forecast value measure for snapshot 1Snapshot 1 Value =var snapshot = values('Snapshot 1'[Snapshot])return SUMX(FILTER('Sample';'Sample'[Snapshot] in snapshot);'Sample'[Forecast])Step 3: Create a Forecast value measure for snapshot 2Snapshot 2 Value =var snapshot = values('Snapshot 2'[Snapshot])return SUMX(FILTER('Sample';'Sample'[Snapshot] in snapshot);'Sample'[Forecast])Step 4: Create a measure that defines a colorHighlight changes =SWITCH(TRUE();[Snapshot 1 Value] = [Snapshot 2 Value];"Gray";[Snapshot 1 Value] < [Snapshot 2 Value];"Green";[Snapshot 1 Value] > [Snapshot 2 Value];"Red")Step 5: Viz it.You can also add another measure eg. difference between and after 🙂
Hope that this help you.
Thanks for sending this through.
Let's assume my Excel file contains multiple tables and a snapshot is taken weekly and stored in a SharePoint folder. Will this still work?
This is how my Excel file will be captured and stored. The idea is to have it complety automated. The spreadsheet I have contains about 10 tables all which I'd like to see if there has been any changes.
I'd ideally want to show it in the format below if possible
FU,
Yes, I think so. Let's assume that you have excel files (all of them with same columns and formats; different values & number of rows accepted) in same folder, then you're using "Import from folder" function, transform the data and ends up with a table that contains all the columns and values from all files, right? On your screenshot there is a column "Name" which represents file name that the data is coming from. In my example this column is calles "Snapshot". Rest of the solution is what I've discribed in previous post. Try it.
Also - my Solution is represented in one table, but you can do two tables if you want, so you can achive desired viz effect.
Ask, if you have some doubts.
Something like the below. The boxes on the left are buttons and highlight in red to show if there are changes in Table 1
This is great - just on your first point on tables not being the same, the overview table in 'Power BI TEST' and 'Power BI TEST2' have the same format and columns. The only difference is the data where I've inserted CHANGED
