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:
Still not clear to me. Both files have dozens of sheets. Which sheet from each file should be used for the comparison?
- FU3 years agoHelper IV
I would use 'Overview' 'Milestones' 'Project Risks' 'Portfolio Risks' but for example purposes lets compare 'Overview' and 'Milestones'?
- lbendlin3 years agoSuper User
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:
- FU3 years agoHelper IV
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 🙂
- FU3 years agoHelper IV
All the tabs that are open. Should be a out 10ish tables
- lbendlin3 years agoSuper User
feel free to add the code for that, based on my sample code.