This case study examines how PureSip Beverages, a regional distributor of major beverage brands, streamlined its reporting process through Power BI automation utilizing Power BI Desktop and folder connections.
PureSip Beverages specializes in the distribution of popular beverage brands — Coke, Fanta, Sprite, Dasani, and Powerade. They supply products to major retail chains, including Costco, Walgreens, Target, and Walmart.
As operations expanded, PureSip’s decentralized reporting system became a major bottleneck. Each regional office manually maintained spreadsheets, resulting in:
- ❌ Inconsistent and delayed reports
⚠️ Manual consolidation errors- 🔒 Limited visibility into company-wide performance
By integrating Power BI with a folder-based data connection, PureSip automated data ingestion and reporting — enabling real-time dashboards and data-driven decision-making across leadership teams.
Key Goals:
- 📂 Centralize sales data collection via a shared Power BI folder connection
- 🔄 Automate data refreshes and eliminate manual consolidation
- 📈 Track sales performance by retailer, brand, region, and manager
- 💡 Enhance decision-making through interactive, real-time analytics
| Metric | Value |
|---|---|
| Total Sales | $1.21M |
| QTD Sales | $2.3M |
| Top Retailer | Walmart ($392K) |
| Top Beverages | Dasani ($234K), Coca-Cola ($228K) |
| Regional Leader | Allen ($392K in sales) |
- Sales by State: California, Texas, and New York
- Sales by Retailer and Beverage Brand
- Monthly Sales Trends
- Regional Manager Performance
- Average Sales by Payment Interval
- KPI Tracker for revenue and growth targets
- 🗂 Centralized Uploads: Retailers drop quarterly spreadsheets into a shared folder
- ⚡ Live Data Model: Power BI automatically refreshes when new files are added
- ⏱ Reduced Workload: Eliminates manual file merging and data entry
- ✅ Improved Accuracy: Consistent formats and live updates ensure reliable insights
Automating data refreshes through the local folder connection freed up valuable time for regional teams and retailers. They could focus on core operations instead of repetitive data entry and manual consolidation.
By eliminating manual manipulation, the process minimized errors and ensured consistent, reliable data across all locations — enhancing trust in analytics outcomes.
Real-time data availability enabled up-to-date reporting and trend analysis, empowering PureSip to react quickly to emerging sales patterns and opportunities.
The folder connection model is fully scalable — if PureSip expands to new regions, no structural changes are needed for data ingestion or refresh processes.
- Power BI Desktop – Data modeling, visualization, and KPI dashboards
- Power Query (Folder Connector) – Automated data ingestion and transformation
- Data Modeling & Relationships – Linking retailer, region, and sales data
- DAX Measures & Interactive Visuals – Enabling dynamic exploration and real-time insights
In Power BI Desktop:
Home → Get Data → Folder → Browse → Select the shared folder containing all regional sales files
This automatically pulls in all Excel files from the folder.
Once files are loaded, use Power Query to clean and prepare data.
Establish relationships between key tables (e.g., Sales, Retailers, Regions, Products) using Model View in Power BI.
Add KPIs for revenue, profit, and time-based performance.
Include visuals such as:
- Bar charts for sales by retailer
- Map visuals for geographic performance
- Line charts for monthly trends
- Card KPIs for total and QTD metrics
Use the Power BI Service to schedule automatic refreshes:
Settings → Datasets → Scheduled Refresh → Enable → Set refresh frequency
Through this automation, PureSip Beverages achieved:
- 📉 75% reduction in report preparation time
- 🧩 Error-free consolidation across regional data sources
- ⚙️ Instant access to company-wide performance metrics
- 💼 Enhanced executive visibility for strategic planning
PureSip’s transformation demonstrates the power of Power BI automation — turning disconnected spreadsheets into a live, scalable data intelligence system that drives real-time insights and operational efficiency.
🧠 From manual reporting to automated intelligence — empowering PureSip Beverages to make smarter, faster business decisions.