Power Query Slow Update

dgantony

New Member
Joined
Mar 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi All
I'm a PQ newbie and have been learning to do my tasks with the help of the internet. Thank you for countless times you all have helped.

For this particular project, I've basically solved the problems using the answers I found on the internet. The issue is that it's taking too long to update. Here's what I'm trying to do.
  1. Grab data from the European CDC that has the daily update of COVID-19 cases and deaths by country
  2. Put it in a table and use PQ to transform the data to get the following:
    1. Calculate the running total of the daily cases so I can view the cumulative cases
    2. Calculate the first day of case in each country so I can time-align the curves
The PQ works the way I want but takes almost 10 mins to update. I would greatly appreciate any help in making this code work faster.

Is there a way to upload the file? I don't see the option on here.

Here's the mcode:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="COVID19Data"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"DateRep", type date}, {"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Countries and territories", type text}, {"GeoId", type text}}),
    RemoveExtraColumns = Table.RemoveColumns(ChangeType,{"Day", "Month", "Year"}),
    RenameCountryColumn = Table.RenameColumns(RemoveExtraColumns,{{"Countries and territories", "Geography"}}),
    SortRows = Table.Sort(RenameCountryColumn,{{"Geography", Order.Ascending}, {"DateRep", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(SortRows, "Row", 1, 1),
    AddRunningTotal = Table.AddColumn(AddIndex, "Running Total", each let Group=[Geography], Row=[Row] in List.Sum(Table.SelectRows(AddIndex, each [Row]<=Row and [Geography]=Group)[Cases])),
    RemoveZero = Table.SelectRows(AddRunningTotal, each ([Running Total] <> 0)),
    GroupGeographies = Table.Group(RemoveZero, {"Geography"}, {{"GeoGroup", each _, type table [DateRep=date, Cases=number, Deaths=number, Geography=text, GeoId=text, Row=number, Running Total=number]}}),
    AddGeoIndex = Table.AddColumn(GroupGeographies, "GeoIndex", each Table.AddIndexColumn([GeoGroup],"Day Number",1,1)),
    RemovedGeoGroup = Table.RemoveColumns(AddGeoIndex,{"GeoGroup"}),
    #"Expanded GeoIndex" = Table.ExpandTableColumn(RemovedGeoGroup, "GeoIndex", {"DateRep", "Cases", "Deaths", "Running Total", "Day Number"}, {"DateRep", "Cases", "Deaths", "Running Total", "Day Number"})
in
    #"Expanded GeoIndex"

Thanks and stay safe...
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dgantony

New Member
Joined
Mar 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you mole999 for the quick response. Sorry, I should have been clear in my original post. The data download itself is manual and I copy paste the data into an excel table. The issue is really the sub-optimal mcode I've stitched together from multiple sources. :)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Thank you mole999 for the quick response. Sorry, I should have been clear in my original post. The data download itself is manual and I copy paste the data into an excel table. The issue is really the sub-optimal mcode I've stitched together from multiple sources. :)
Then its likely to be machine spec, and formulas i'm guessing, there are a few here that get PQ, I've got lots to learn and no time, the links might help you visualise what is currently reported and stitched together
 

dgantony

New Member
Joined
Mar 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Great! Thank you, mole999.
 

dgantony

New Member
Joined
Mar 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Those links are really cool, just checked them out. I didn't realize that they were related to my question initially. Thought that they were part of your footer. 🤦‍♂️
Thanks for those.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,492
Messages
5,548,362
Members
410,828
Latest member
A9Bosv3
Top