Forum Discussion
Weekly data analysis
Hi Community!
I need help to automate weekly data analysis task.
Every week I download a spreadsheet containing sales of my store. The spreadsheet contains data as follows:
| product_id | product_category | status | department | … and many other columns |
| apple_green_1 | apples | in_store | department_1 | ... |
| apple_green_2 | apples | sold | department_2 | ... |
| peach_1 | peaches | in_store | department_1 | ... |
| … other unique product IDs | ... | ... | ... | ... |
I store those weekly reports on my local PC, manually calculate 5 metrics (like “number of products sold by month”, “number of apples by month”) and use them in Excel’s charts to show weekly sales.
I want to automate this whole process using Power BI. And I would also like to have more than 5 metrics and even have an ability to create new metrics. Therefore, the weekly reports should be re-analyzed when I decide to add a new calculation (let’s say, “sales by department by month” or “sales by product_category by month” which I don’t currently have). My questions are:
- What is the best way to automate Power BI to pull and analyze all my weekly reports stored in a folder? Adding a new data source every week?
- Those files are about ~200 MB in size, how much time would Power BI need to analyze 4*12 reports to show data for the whole year? Would it die when I approach December? Is there a better way to do it?
- What is the best way to calculate my metrics across all reports and show them a on a “by month” chart? Do I need to copy/paste all my Transformation steps/Measures when I add a new week’s report?
Power BI can handle that, but you can make it even easier by setting up incremental refresh as demonstrated in this video. It uses SharePoint for file storage (which you should consider), but could easily be adapted for use with the Folder connector.
Incremental Refresh with SharePoint Files - YouTube
Pat
3 Replies
Power BI can handle that, but you can make it even easier by setting up incremental refresh as demonstrated in this video. It uses SharePoint for file storage (which you should consider), but could easily be adapted for use with the Folder connector.
Incremental Refresh with SharePoint Files - YouTube
Pat
Thank you, that is exactly what I was looking for! And I will migrate everything to SharePoint, thanks!
Could you show me how to pull the same metric (let's say, number of rows) from 12 monthly files in my SharePoint folder and show them on a chart with month as X-axis? Should I use Measures for that?
Glad that will work for you. Once you combine the files, you have a single table with all your data. You can choose to keep the original file name (or date), so you can easily create visuals that show the row count from each (yes, with measures). You should next consider adding a Date table to your model, to easily slice/dice by day, week, month, year, etc.
Pat
