Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yshmelev
Frequent Visitor

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 statusdepartment… and many other columns
apple_green_1applesin_storedepartment_1...
apple_green_2applessolddepartment_2...
peach_1peachesin_storedepartment_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?




1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee
yshmelev
Frequent Visitor

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

Microsoft Employee

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.