Need advice or assistance - Incremental changes between large numbers of large tables

[Sam_Vere]

Hello all,

I'm hoping for some help or advice in how I might resolve this problem.

Basically, I need to try to track & summarise the week-on-week changes in status for a fairly large number of unique 'things'.

I currently do not have direct access to the datasets involved - they come from an external database in the form of weekly extracts.

Each week's dataset is a CSV file that currently averages 75,000 to 85,000 rows and consists of:

• Unique identifier for each 'thing' (9-digit number)
• Primary Status (Text String)
• 'Sub'-status (Text String)
• Date that Primary Status most recently changed
• Date for the 'week commencing' of the week the specific dataset refers to

What I want to be able do is to determine the status changes from one week to the next, categorise the change, then have summarised counts of those categories.

The categories I want to use are:

1. 'No change' (Both weeks the same status) - ideally this should exclude any instances where there is no data for any given 'thing' in either week being compared
2. 'New' ('thing' appears in the later week but not the earlier)
3. 'Removed' ('thing' appears in earlier week but not the later)
4. 'Changed', When there has been an actual status change between one week & the next. This should return the 'new' status one way or another.

Owing to the weekly nature of the extracts, merging tables is likely to be inpractical.

Any advice or assistance gratefully accepted.

Thanks in advance.

[Eric_Zhang · Accepted solution]

@Sam_Vere

How do you maintain the update work? Supposing there're two tables(current week & last week) in your report, in this week's load, you just remove the "last week" and rename the "current week" as "last week", load the current week's CSV as table "current week"? In this case, please check the attached pbix.

Summary for the pbix.

Renaming and re-loading is a little cumbersome, if there's any database avaiable in your case, I will load the data into database and maintain the calculated table in database. Then connect to the database, create report in power bi and publish, set up a gateway. Then nothing needs to change from power bi end any longer. Things can be much easier if you can automate the data loading, with a tool like SSIS.

[Sam_Vere]

Apologies for the delay in replying - outside circumstances.

Part of the issue is that I need to be able to produce/maintain a 'history' of changes over time, at least within a given year. This means that I could end up dealing with up to 50+ tables by late December.

This said I will try out your example file once I've had an opportunity to upgrade my version of PBI Desktop.

Thanks again.