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.

[FU]

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

[bolfri]

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 1

Snapshot 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 2

Snapshot 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 color

Highlight 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.

[FU]

Something like the below. The boxes on the left are buttons and highlight in red to show if there are changes in Table 1

[FU]

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

 

 

[bolfri]

@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.

[FU]

@bolfri 
This is very helpful and I think very close to the solution.

Also it's worth noting - I have multiple tables in my Excel file. For example in the last image i've attached you can see 'Overview' and 'Milestones' in the top left and the idea with those is to have them as buttons where they are conditionally formatted to show if there are any changes in those tables. Does that make sense?

For example If i have multiple buttons down the left all representing a table in my Excel file, in a reset view I want to be able to:

Step 1: select two reports/dates e.g. Power BI Data1 vs Power BI Data2

Step 2: on the left hand side where my buttons will be - be able to see which tables have changes in them by filling the button in green for example.

Step 3: click on the highlighted button on the left and then be able to see where the changes are on the tables as per your example above. 

Does that make sense or have I confused you

[bolfri]

Yes. It can be done in Power BI. Even if you are using different tables from few excel files. The main question here is: do this tables are same OR they have different columns/format types?

If same then you can union them into one table and have sample data like this:

FileName aka Snapshot	DataName	Another Column	Some Value
Power BI Data.xlsx	Overview	Some KPI 1	50
ower BI Data.xlsx	Milestne	Some Other KPI	55
Power BI Data 1.xlsx	Overview	Some KPI 1	60
Power BI Data 1.xlsx	Milestne	Some Other KPI	60

If different then you can have 2 tables: one for Overview statuses, one for Milestone statuses and create a button to switch between bookmarks with this two tables. This buttons can be conditionaly formated if there are some changes between selected files.

You can also prepare a button to filterout only rows where you have some changes (something like: See details).

[FU]

The tables are the exact same format as they are a snapshot of the data on a weekly basis. NO changes to column names/formats or quantity of columns

[bolfri]

So if I am correct:

You have an excel files eg. Power BI Data Week xyz.xlsx and in this file you have 2 Tables (in one sheet or different one) One for Overview and one for Milestones. TABLES (both Overview and Milestrones) ARE THE SAME but contains different values in one column that can change in next file. Right?

And next file eg. Power BI Data Week abc.xlsx have same format, same tables etc but different values in values column on it.

[FU]

For example

Overview Table on week 1:

Project Name	Project ID	KPI	Status	Date	Risk
Name 1	ID 1	KPI 1	Status A	Date A	Risk B
Name 2	ID 2	KPI 2	Status B	Date B	Risk C

Overview Table on week 2 (Changes highlights in Bold)

Project Name	Project ID	KPI	Status	Date	Risk
Name 1	ID 1	KPI 1	Status H	Date A	Risk M
Name 2	ID 2	KPI 2	Status N	Date G	Risk C

Something like this. I have about 10 tables all in different sheets which would follow the same format as above

[FU]

YES! That is correct. Would it help if I sent a sample PBI file? Can I even do that on here - if so how?

[bolfri]

Yes, sample file would be great. You can zip an example excel files (for week 1 and week 2) with few sheets and table with sample rows on them. Then a PBI file (if you alreday have it).

You can post it on website eg. https://wetransfer.com/ and give here a link so anyone can help.

We will figure out something 🙂

[FU]

I have uploaded 2 Excel files here. I for each snapshot week. 

https://we.tl/t-kN7fYQnfmn

[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]

Hi @FU,

I can't send long messages, so I'll put the instruction how to create a tooltip right here.

Create a new page. Let's call it: Tooltip.

Change the General settings on that page to be smaller than usual eg 400px x 600px in my case.

Add a visualisation that you want (in my example it was a table with column listed below). Note that "Flag" column is actually Changes detector measure from before, but it's also formated as a icon only. 

Turn off the "Keep all filters" switch and put there an Index and SourceTable.Name field only.

At this step you should see something like this:

Come back to the first page and from Ribon open Sync slicers panel.

Click on the Source.Name slicer (FileFilter_1) and turn on the sync slicers like this:

So there is a sync between Sample and Tooltip page, but only on Sample page this slicer will be visible.

Do that same thing for Source.Name slicer (FileFilter_2), so both filters will be applyied on Tooltip page.

Click on the table witch detected changes, go to format options and then generals:

Turn On Tooltip section

Type: Report page

Page: Tooltip (or another name if you changed it before)

It should work 🙂

[FU]

Thank you this worked. The Issue I am having is my tooltop is a long list - how can I scroll down tooltip? If i move my mouse my tooltip will dissapear

[bolfri]

Unfortunetelly this can't be done. Tooltip it's a tooltip. 😄

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough

You can copy Tooltip page and turn off tooltip option. In that case you can Drillthrough the table to that page and scroll.

[FU]

Can I select which columns to show on tooltip or does it include everything?

[FU]

Excellent I will give this a try - just to confirm I do not need to make any changes to my back end data (like the steps you mentioned yesterday) correct?

[bolfri]

No, you don't need to. 🙂 You can apply everything on the project that you're working on.