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!
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:
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:
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?
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 🙂
Both Power Query and DAX offer functions for that.
Join Types in Power Query – Part 1: Join Types - Excel UnpluggedExcel Unplugged
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
