I collect data daily via URL download to csv (standard tabular format). I use Excel Power Query to transform and summarise this data (groupby row count by column header name) so that it is crunched from rows in excess of 100,000 down to hundreds (Table 1). I then create a second Excel Power Query which adds the contents of Table 1 to an historic version of the same data (Table 2). Finally, I use another Power Query, this time in Power BI, to draw the contents of Table 2 to Power BI where I visualise and share the summarised, historic data with stakeholders. Clearly this is a very manual exercise, but I have yet to find an alternative daily update methodology which simplifies the entire process (in particular, the historic update with daily data). Ideally, I need a process which operates automatically, daily, directly from source to Power BI to stakeholder (ie without needing to do the transformation in Excel)
Any advice or alternative courses of action much appreciated.
Thanks.
PS Recently I have connected the external URL above to MS Dataverse (so that every new line item in the source data is automatically collected real time). MS Dataverse can be connected directly to Power BI (but not to Excel as far as I can see).
