Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
FU
Helper IV
Helper IV

[STILL UNSOLVED] Tracking changes between tables

Hey all,

 

I have a sharepoint folder which contains weekly snapshots of my Excel data file. 

 

I would like to be able to compare each file against one another and show where the changes are between them - i.e. I am trying to create a 'Track Changes' tab.

 

E.g. if a client wants to view what changes have been made to financial data from week 1 and week 3, they can select week 1 and week 3 filters and using conditional formatting (or some method) see if there are any changes in that column

 

Image 1: This is how I capture my weekly snapshots

FU_0-1671530342579.jpeg

 

Image 2: I then connect my Power BI dashboard to my SharePoint folder and read all the files as shown below.

FU_1-1671530395186.jpeg

 

Image 3: Now for the visual part, I want to be able to select 2 reports/dates (as shown in the filter) and immediately see where there are changes between the 2 reports. The current view shows the full table from week 1 and week 3 but I'm only interested in seeing where there has been changes.

FU_0-1671530548068.jpeg

 

 

Any suggestions would be hugely appreciated!

1 ACCEPTED SOLUTION

Here would be the query for Overview

 

let
    Overview = (f)=> let S = Excel.Workbook(f),
    Overview_Sheet = S{[Item="Overview",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Overview_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers",
        Source = Folder.Files("C:\Users\xxx\Downloads\Test folder"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Overview([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Report Entry Date", "Project Name", "Project ID", "Programme ID", "Project Stage", "Start Date", "EIS Forecast", "AFC Baseline (£m)", "Current AFC (£m)", "Project Maturity ID", "Configuration State ID"}, {"Report Entry Date", "Project Name", "Project ID", "Programme ID", "Project Stage", "Start Date", "EIS Forecast", "AFC Baseline (£m)", "Current AFC (£m)", "Project Maturity ID", "Configuration State ID"})
in
    #"Expanded Custom"

 

 

This will load the snapshots into a single query.  Next you need to decide which columns to compare from the Overview sheet. For example the project stage:

lbendlin_0-1671728486926.png

 

View solution in original post

27 REPLIES 27
lbendlin
Super User
Super User

Still not clear to me. Both files have dozens of sheets.  Which sheet from each file should be used for the comparison?

All the tabs that are open. Should be a out 10ish tables

feel free to add the code for that, based on my sample code.

I would use 'Overview' 'Milestones' 'Project Risks' 'Portfolio Risks' but for example purposes lets compare 'Overview' and 'Milestones'?

Here would be the query for Overview

 

let
    Overview = (f)=> let S = Excel.Workbook(f),
    Overview_Sheet = S{[Item="Overview",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Overview_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers",
        Source = Folder.Files("C:\Users\xxx\Downloads\Test folder"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Overview([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Report Entry Date", "Project Name", "Project ID", "Programme ID", "Project Stage", "Start Date", "EIS Forecast", "AFC Baseline (£m)", "Current AFC (£m)", "Project Maturity ID", "Configuration State ID"}, {"Report Entry Date", "Project Name", "Project ID", "Programme ID", "Project Stage", "Start Date", "EIS Forecast", "AFC Baseline (£m)", "Current AFC (£m)", "Project Maturity ID", "Configuration State ID"})
in
    #"Expanded Custom"

 

 

This will load the snapshots into a single query.  Next you need to decide which columns to compare from the Overview sheet. For example the project stage:

lbendlin_0-1671728486926.png

 

Also would you be able to send the pbix file for the example you have here? 

The proposed approach that I attached further up can handle any number of files as long as they are in the same folder.

I can see you've filtered only for "project stage" how can I format it to show all the columns in the overview table. 

 

So essentially I want to be able to filter by project name then see all the associated columns in that row and highlight only the changed values against previous snapshot files

You can choose to create comparison measures for each of the columns you want to compare. Or you could unpivot your columns into attributes and use a single measure.

Ideally I'd want to have it all on one query. If I un pivot my columns wouldn't I lose the ability to filter by project name?

No, "Project Name" would be one of the values in the "Attribute" column after the unpivoting.

 

To be clear:  You are asking for  a comparator between an arbitrary number of Excel files with an arbitrary number of sheets with an arbitrary number of rows (and no clear primary key) and you want all this in a single query.  This is far beyond what Power BI can do natively, and is stretching it if you compromise and create multiple (many) queries.

Let's assume I have many snapshots, am I able to view and compare more than 2 files at once or am I restricted to viewing and comparing 2 files at a time?

Do you have the pbix file you can share for this example pls?

see attached.

I am trying to achieve this format. 

https://community.powerbi.com/t5/Desktop/Viewing-changes-in-historic-snapshot-reports-urgent/m-p/298...

 

1) slice between any two dates and be able to view which date created reports fall within those dates.

 

2) select from the drop down "portfolio" "programme" "project" to see changes specifically related to a particular project or programme etc

 

3) then filter from the tables. E.g. I might filter for project A which had a change in the overview tab as well as the milestones tab. Using the tables to filter between tabs.

 

I have attached the excel files with a screenshot of the format I am trying to achieve. Been trying to figure this out for a few days now

 

If anyone's able to whip this up on power BI and share some steps / pbix file I'd really appreciate it 🙂

lbendlin
Super User
Super User

Thanks for the links. I'm afraid they wernt much help to what I'm trying to do

You would create table variables, one for Week 3 and one for Week1. Then you run EXCEPT() against these to see what data doesn't match between them.

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hey did you manage to get a chance to look at the sample data?

I have updated the body of this post with sample data and screenshots - hope it clarifies!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors