Forum Discussion
[STILL UNSOLVED] Tracking changes between tables
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:
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:
I am trying to achieve this format.
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 🙂
Do you have the pbix file you can share for this example pls?
see attached.
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?
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