Forum Discussion
[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
Image 2: I then connect my Power BI dashboard to my SharePoint folder and read all the files as shown below.
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.
Any suggestions would be hugely appreciated!
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:
27 Replies
Both Power Query and DAX offer functions for that.
Join Types in Power Query – Part 1: Join Types - Excel UnpluggedExcel Unplugged
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-p/963216
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
Still not clear to me. Both files have dozens of sheets. Which sheet from each file should be used for the comparison?
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:
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.
