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:
Thanks for the links. I'm afraid they wernt much help to what I'm trying to do
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-p/963216
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
- FU3 years agoHelper IV
Great thanks, I will post sample data for further clarification!
- Greg_Deckler3 years agoCommunity Champion
FU Respond to your PM, if you post sample data as text or a link to your PBIX this will be much easier to solve.
- FU3 years agoHelper IV
Let's take the below table as an example. Let's assume every week I run a flow to automatically grab a snapshot of the current data in my Excel file and store these files into a SharePoint folder which is connected to my PBI file.
Example -
The week 1 table is showing my data at a given snapshot date.
Week 1:
Project Name Project ID Programme ID Forecast Status A ProjectID 1 ProgrammeID 1 £500,000.00 Status A B ProjectID 2 ProgrammeID 2 £500,000.00 Status B C ProjectID 3 ProgrammeID 3 £500,000.00 Status C D ProjectID 4 ProgrammeID 4 £500,000.00 Statuc D One week later my power flow takes a snapshot of my Excel file and stores Week 2 into my SharePoint folder
Week 2: (changes from week 1 and week 2 in 'Forecast' and 'Status' columns.
Project Name Project ID Programme ID Forecast Status A ProjectID 1 ProgrammeID 1 £500,000.00 Status A B ProjectID 2 ProgrammeID 2 £640,000.00 Status H C ProjectID 3 ProgrammeID 3 £700,000.00 Status G D ProjectID 4 ProgrammeID 4 £500,000.00 Statuc O After several weeks I would have multiple snapshots of this table with data changing each week. I want to create a visual which allows me to see which field have been changed. Something like the below image:
If i select week 1 and week 3 in my filters, the column+row which have changes in them would be highlighted.
I hope this is clarifies? If not please let me know!
Thanks again
- FU3 years agoHelper IV
I have updated the body of this post with sample data and screenshots - hope it clarifies!
- FU3 years agoHelper IV
Hey did you manage to get a chance to look at the sample data?