Power Query Slow Update

[dgantony]

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:

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

 

[mole999]

Unrealted though I would expect the sources are heavily mined by many hence the slow update

> Experience

> Operations Dashboard for ArcGIS

 

[dgantony]

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]

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]

Great! Thank you, mole999.

 

[dgantony]

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.

 

[sandy666]

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

post a link to the shared excel file via googledrive, onedrive or any similar