Collection of solutions to real-world Excel & BI challenges from Excel BI community. Learn how to solve complex data problems using Python, PySpark, and Power Query (M).
- 🎯 About This Repository
- 📂 Challenges by Source
- 🛠️ Technologies Used
- 📋 Challenge Index
- 🚀 Getting Started
- 🤝 Contributing
- 📚 Resources
This repository documents solutions to community-driven BI challenges:
✅ Challenges from Excel BI — Official Excel challenge problems
✅ Challenges from OMID — Data transformation & analysis problems
✅ Multiple Solution Approaches — Python, PySpark, Power Query (M)
✅ Complete Documentation — Problem statement + step-by-step solution
✅ Runnable Notebooks — Jupyter notebooks & Power Query scripts included
✅ Real Data Sets — Actual data files for testing & learning
Learning Goals:
- Master data transformation techniques
- Compare solution approaches (Python vs PySpark vs M)
- Understand distributed computing with Spark
- Optimize query performance
The Excel BI Community publishes weekly Excel challenges on LinkedIn. This section contains my solutions.
Challenges Included:
| # | Challenge | Problem | Solutions | Difficulty |
|---|---|---|---|---|
| 403 | Excel Challenge 403 | Generate 5-year intervals with cumulative sums and percentages | PySpark | ⭐⭐ |
| 410 | Excel Challenge 410 | Optimize complex data queries using Power Query | M Language | ⭐⭐⭐ |
| 416 | Excel Challenge 416 | Generate sequences from strings (numeric patterns) | PySpark + Python | ⭐⭐⭐⭐ |
→ Explore All Excel BI Solutions
Solutions to data challenges from OMID Motamedisedeh, focusing on practical BI scenarios.
| Technology | Purpose | Examples |
|---|---|---|
| Python | Data cleaning, analysis, scripting | Pandas, NumPy, data processing |
| PySpark | Distributed processing at scale | Large datasets, transformations |
| Power Query (M) | ETL in Excel/Power BI | Complex queries, merges, pivots |
| Jupyter Notebooks | Interactive learning & documentation | Step-by-step problem solving |
| Git | Version control & collaboration | Tracking solutions |
EXCEL_BI/
├── 403_EXCEL_CHALLENGE/
│ ├── README.md # Challenge statement & approach
│ ├── Excel_Challenge_403.ipynb # PySpark solution in Jupyter
│ ├── files/
│ │ ├── Excel_BI.png # Challenge screenshot
│ │ └── data.xlsx # Sample data
│ └── solution.py # Python/PySpark code
│
├── 410_EXCEL_CHALLENGE/
│ ├── README.md
│ ├── Power_Query_Solution.m # M language code
│ └── files/
│ └── Challenge_410.pbix # Power BI example
│
└── 416_EXCEL_CHALLENGE/
├── README.md
├── Sequence_Generator.ipynb # Multi-approach solution
└── files/
└── sequences_output.csv
OMID_BI/
├── Challenge_001/
├── Challenge_002/
└── ...
git clone https://github.com/CSalcedoDataBI/BI_Challenges.git
cd BI_ChallengesNavigate to any challenge folder:
cd EXCEL_BI/403_EXCEL_CHALLENGEOpen README.md to understand the problem:
cat README.md # or open in your editorjupyter notebook Excel_Challenge_403.ipynbpython solution.py- Open the
.pbixfile in Power BI Desktop - Go to Data → Transform Data
- Review the
solution.mcode in Power Query Editor
- Modify the data source paths
- Change parameters (thresholds, intervals, etc.)
- Test with your own datasets
- Compare different approaches
Generate the sum and percentage for 5-year intervals from a year-value dataset.
Input:
Year | Value
------|-------
1990 | 100
1992 | 150
2000 | 200
2005 | 300
2015 | 400
Expected Output:
Year Group | Sum of Value | % of Value
-----------|--------------|----------
1990-1994 | 250 | 14%
1995-1999 | 0 | 0%
2000-2004 | 200 | 11%
2005-2009 | 300 | 17%
2010-2014 | 0 | 0%
2015-2019 | 400 | 23%
Grand Total| 1750 | 100%
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, floor, sum as _sum, round
spark = SparkSession.builder.appName("Challenge_403").getOrCreate()
# Load data
df = spark.read.csv("data.csv", header=True)
# Calculate 5-year intervals
df_grouped = df.withColumn(
"YearGroup",
((col("Year") - 1990) / 5).cast("int") * 5 + 1990
).groupBy("YearGroup").agg(_sum("Value").alias("Sum_Value"))
# Calculate percentages
total = df_grouped.agg(_sum("Sum_Value")).collect()[0][0]
df_result = df_grouped.withColumn(
"Percent",
round((col("Sum_Value") / total) * 100, 0)
)
df_result.show()- Start with Challenge 403 (⭐⭐ difficulty)
- Follow the Jupyter notebooks step-by-step
- Compare Python vs PySpark vs Power Query approaches
- Modify code and experiment
- Use solution patterns for your own projects
- Adapt SQL/PySpark queries for similar problems
- Learn Power Query (M) idioms and best practices
- See how to structure complex data workflows
- Share your own solutions via pull requests
- Add new challenges from other communities
- Improve documentation & code comments
- Help others learn
Have a new challenge or solution? We welcome contributions!
- Fork the repository
- Create a folder:
CHALLENGE_SOURCE/NNN_DESCRIPTION/ - Add files:
README.md— Problem statement & your approachsolution.pyorsolution.m— Your codefiles/— Data files & screenshots
- Push & open a Pull Request
- ✅ Include the original problem statement (with source link)
- ✅ Document your approach & logic
- ✅ Provide runnable code (Jupyter or Python script)
- ✅ Add sample data or instructions to get it
- ✅ Show expected output with screenshots
- ✅ Compare multiple approaches if applicable
- 📧 Email: csalcedo90@gmail.com
- 💼 LinkedIn: Cristobal Salcedo
- 🐛 Issues: GitHub Issues
- 📖 PySpark Documentation
- 📖 Power Query (M) Reference
- 📖 Pandas Documentation
- 📖 Jupyter Notebooks
- 📖 Power BI, Deneb & Fabric guides at csalcedodatabi.com — tutorials on Power BI visuals, Deneb/Vega-Lite templates and Microsoft Fabric data agents (in Spanish)
MIT License — see LICENSE for details.
You're free to use, modify, and distribute these solutions for educational and commercial purposes.
If these solutions help your learning journey:
⭐ Star this repository — Help others find it
🔄 Fork & contribute — Add your own solutions
💬 Share feedback — Tell us what you'd like
Made with ❤️ by Cristobal Salcedo
Powered by Python, PySpark & Power Query