Convert cumulative daily history to new daily

Power Query
1

Working with Challenge 11 COVID tracking, I found a way to convert cumulative history to daily new cases. Here are the steps

Sort on your group (Country/State) + date so all your data.
Add a 0-based index
Duplicate the table and add a 1-based index to this table
Merge the two tables on the indexes and expand to show country/state
Now its a simple new column

  • if state/country matches then 0-based value - 1-based value else 0-based value
    Remove extraneous columns
    Disable load for 1-based index

The attachment takes small piece of the cases. When I tried the entire file, it was rather slow.cum2new.pbix (146.9 KB)

1 Like
2

@markperrone,

Great post. I used the exact same technique to transform the DC #11 data from cumulative to marginal daily values. If folks are interested in employing this technique, in addition to Mark’s post, Imke Feldmann has an excellent blog post and video on the technique:

– Brian

1 Like
3

interesting…I didn’t know about biccountant. He created lists instead of adding an index and gets a significant performance gain.

4

Hi @markperrone, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

5

Hi @markperrone, we’ve noticed that no response has been received from you since the 27th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!