cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam_Vere Frequent Visitor
Frequent Visitor

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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

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

@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.

Capture.PNG

 

 

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.

 

2 REPLIES 2
Moderator Eric_Zhang
Moderator

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

@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.

Capture.PNG

 

 

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 Frequent Visitor
Frequent Visitor

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

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.

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 284 members 3,089 guests
Please welcome our newest community members: