Tracking data changes

[FU]

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!

[bolfri · Accepted solution]

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.

[bolfri · Accepted solution]

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 File1

Value 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 File2

Value 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 not

Changes 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 changes 

Step 6. Put all filters on the page and viz it as you want 🙂

Final effect: