Forum Discussion

FU's avatar
FU
Helper IV
3 years ago
Solved

[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 bet...
Need Help
  • lbendlin's avatar
    lbendlin
    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:

     

lbendlin's avatar
lbendlin
Super User
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:

 

FU's avatar
FU
Helper IV
3 years ago

I am trying to achieve this format. 

https://community.powerbi.com/t5/Desktop/Viewing-changes-in-historic-snapshot-reports-urgent/m-p/2988137#M1020648

 

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 🙂