How can we merge data when New York is listed at NYC, New York City and NY, NY? This is a common problem and there’s a new feature in Excel’s Power Query that can help us solve this. We can build a Transformation Table in Power Query as part of making a join. This video […]
Did you ever think that you could write a formula with a picture of grapes and have the result be a picture of a train? It’s possible to add emojis in Excel. But it gets better! This isn’t like adding a picture just to make things pretty. No no. The emojis can be used like […]
One option when loading a query from Power Query is: Load to Data Model (or create Relationships). In this video that’s what we cover, and why. In this video we have 3 tables of data and we’d like to answer questions that can only be answered if the data were all in one place. We […]
We’ve used the dynamic array FILTER function to filter for multiple criteria that will return a lot of data. But what happens when we need to filter for things that don’t fit neatly into a group or category? In this example we have a list of names and want to filter for specific people in […]
Power Query is fantastic, but there’s an ongoing need to do the equivalent of an approximate match like we do when we use VLOOKUP-True to assign categories to values. This video shows how to assign values by “binning” which is done by using Column by Example and getting help from Power Query. Power Query will […]
This one is about over-thinking. A friend asked how to average the top 3 of 4 scores. I started thinking about Dynamic Arrays, unpivot and maybe the opportunity to show off some Power Query. But when I went with my first and dullest idea, that’s what gave her exactly what she needed. I used the […]
When we build models in Excel we must anticipate crazy entries. In this tutorial we try to prevent crazy matches between labels and containers. A 4×5 label won’t fit on our 3-ounce spray bottle, and a 1×1 label would look crazy on a 1-gallon jug. First, we use Power Query and Unpivot to organize our […]
For several years I’m maintained my own random data generator in Excel. It makes heavy use of RAND and RANDBETWEEN. However, with the use of Dynamic Arrays and the new RANDARRAY function I’ve been rebuilding the random data generator. RANDARRAY allows so much more flexibility and 4 formulas get me as many names as I […]
When people have to list their start times, end times and their tasks, how can we tell if any of the times overlap? One time I solved this by writing a 25-level nested IF statement to compare each of 25 rows of times against each other. It worked. It was ugly, but it worked. In […]