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:

     

FU's avatar
FU
Helper IV
3 years ago

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

lbendlin's avatar
lbendlin
Super User
3 years ago

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.

  • FU's avatar
    FU
    Helper IV
    3 years ago

    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?

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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.