Weekly data analysis

[yshmelev]

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:

1. 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?
2. 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?
3. 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?

[ppm1 · Accepted solution]

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

[yshmelev]

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?

[ppm1]

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