Forum Discussion
[STILL UNSOLVED] Tracking changes between tables
- 3 years ago
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:
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.
- FU3 years agoHelper IV
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
- lbendlin3 years agoSuper User
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.
- FU3 years agoHelper IV
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?