Power Query Challenge: Weighted Average from the Last 3 Transactions - PQ125
Power Query Challenge: Weighted Average from the Last 3 Transactions - PQ125
In this Power Query Challenge #125, you'll solve a realistic business problem using mostly the Power Query interface and only a small amount of M code.
The task starts with a single text column containing values like:
12;45.5;EUR;Completed
From there, we will:
✅ Split the data into separate columns
✅ Filter only completed transactions
✅ Convert multiple currencies into USD
✅ Create a transaction amount column
✅ Group records by category
✅ Keep only the last 3 completed transactions
✅ Calculate a weighted average price
This challenge demonstrates an important analyst workflow:
Clean → Filter → Transform → Group → Calculate
Many Excel users immediately think about formulas, but Power Query often provides a simpler and more scalable solution.
If you're learning Power Query, data transformation, reporting automation, or preparing for analyst interviews, this challenge is worth trying on your own before watching the solution.
Calculate the Weighted Average Price in USD of the last 3 Completed transactions for each Category.
Weighted Average Price in USD =
Sum(Quantity × Unit Price) ÷ Sum(Quantity)
Can you solve it before the walkthrough?
👇 Let me know in the comments:
Did you use Group By + All Rows, or did you approach the problem differently?
Subscribe for more Power Query challenges, Excel tips, Power BI tutorials, and data transformation techniques.
#PowerQuery #Excel #DataAnalytics #BusinessIntelligence #PowerBI #ExcelTips #MCode #DataCleaning #ExcelTutorial #WeightedAverage #DataTransformation #PowerQueryChallenge #ExcelChallenge #Analytics #howtolearnexcel
Explore more tutorials:
🎥 Power Query Challenges Playlist:
• Power Query: Challenges
🎥 Dynamic Array Formulas Playlist:
• Modern Excel Challenges
#PowerQuery #ExcelTips #HowToLearnExcel
Links:
Challenge Author: Vijay A. Verma
LinkedIn: / excelbi
Challenge Post: / excelbi_challenge-powerquerychallenge-daxc...
Solution: https://docs.google.com/spreadsheets/...