Of more than 200 pizza orders and 20 ingredients, which trio of toppings shows up the most? This was more of a mind exercise because the Excel piece was easier than I thought. One challenge is that we want which 3 show up together the most–even if they are with other items. Example: 3 pizzas: […]
Recently, Ken Puls of ExcelGuru.ca issued a challenge: Isolate the members who have more than one type of membership and show all of their transactions. This was tricky for a few reasons: – One person had 2 transactions but for the same type of membership. We DON’T want that person in the final list. – […]
#ExcelHash is a game we’re going to try out and you get to suggest the 4 ingredients. ExcelIsFun MrExcel.com Leila Gharani MyOnlineTrainingHub Excel Campus and I are going to build something based on your suggestions. It might be useful, it might be patently useless. The goal is to create something integrated that does SOMETHING. Anything. […]
If performance is below standards for 2 consecutive months, something bad is going to happen! Highlighting periods like that are challenging in Power Query because PQ forces us to think in terms of whole columns or whole tables. We can’t easily compare a range of cells against each other and pluck out the offenders. This […]
Too often I see spreadsheets that have models built, complex formulas, etc. The user will come to me and ask for help with automation. But I often discover data quality issues that cause us to back up and assess everything. I was helping someone with his project. Lots of data in multiple workbooks, summary pages, […]
In my previous video I showed a solution to transforming data. We started with students and the courses they took, and converted that to a view that shows, by course, which student took each one. That video showed a one-and-done solution. It wasn’t dynamic; i.e., new students or courses would net be integrated. In this […]
We have a matrix showing Students and the courses they took: Connie took Menu Planning and Whiskey, Bourbon & Rye. But now, we’d like a view that lists the courses and which students took those courses; e.g., Who took the Nutrition course? MarvP in Seattle hit me with this challenge and on one of the […]
Calculating the days until someone’s next birthday or anniversary is a messy affair in Excel. It’s especially messy for people who’s next birthday/anniversary is in the next calendar year. This video shows an easier way to calculate the next birthday in Power Query. It takes a few steps but it’s more direct than the messy […]
Recent videos have included the Drill Down feature that’s in Power Query and PowerBI. My friend, Bart Titulaer asked for more detail about Drill Down because it isn’t so clear. Download the workbook: http://datascopic.net/drilldown In this video I show Drill Down as a means to get down to a value. The scenario: We sell slices […]