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

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?