Tracking data changes

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]

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: