Mr. Excel, Bill Jelen, was presented with a challenge where someone’s data was twisted in a weird sort of way and needed to be untwisted; e.g. the data needed to be flattened. Here is the challenge: https://youtu.be/S9ow-1Jq_rE Bill asked for other solutions than his own. Here’s mine. This is one of those examples where it […]
In the previous video I showed a solution to a problem presented by Alan Murray at ComputerGaga. But Haider Ali asked about missing entries. (To purchase Excel Insights, here it is: https://www.mrexcel.com/store/index.php?l=product_detail&p=381) The challenge requires data to be converted from jumbled lists and into neat rows. The lists are what people have chosen for an […]
Alam Murray at ComputerGaga postd a challenge that someone had asked him to help with. https://www.linkedin.com/posts/alanmurray-computergaga_excel-powerquery-activity-6621158742408867840-lhby Data containing meal selections came in looking like rosters, and weren’t helpful to anyone. So, the data needed to be converted 2 ways. – A count of requests for each offered meal – Each person on a row with […]
In the previous video I showed how to reconcile Christmas payments between 3 siblings. It seemed that classic Excel using SUMIFS was a better solution that Power Query. However, after posting the video, Miguel Escobar offered a simple Power Query solution. But it’s tricky. It involves merging a table with itself and criss-crossing the fields. […]
This video revisits a challenge from a few years ago. 3 siblings needed a simple way to reconcile who owed what to whom after all the Christmas gits had been purchased. For example: If Bo agreed to chip in $200 on gifts that Jay bought, and Jay agreed to chip in $90 on gifts purchased […]
Excel Hash is back! 4 ingredients, 5 contestants and we all had to create a solution that integrates all 4 ingredients. The Contestants: Mr. Excel, Bill Jelen Mynda Treacy Jon Acampora Leila Gharani Oz du Soleil The Ingredients: XOR, a Linked Picture, a Dynamic Array function, an Icon Here is the link to the playlist […]
We put a lot together in this superbly delicious Excel tutorial. We start with the revised XLOOKUP version 3.0 which has the “if not found” component moved to the 4th position instead of the 6th. Then we move to import data from a webpage and clean the data using Power Query’s FILL Down, Text.Length and […]
Duplicate Query in Power Query is underused and under-discussed. This video shows one way to use Duplicate Query to save from doing a lot of work over and over again. This video also shows split column; split column non-digit to digit; a little M-code. There’s great stuff to keep you from having to work so […]
To do training we need fake data (dummy data) to work with. In this video I show how to use RANDARRAY and the TEXT function to create a list of dummy transactions. This solution also takes advantage of Dynamic Arrays and the new calculation engine behind Excel. We also generate a list of fake dates. […]