For this Funky Funktion Friday, we continue expanding on the party list from the previous videos. This time we have 2 issues: 1. A new column is added to the source data, how do we get it to show up in the final list? 2. We need a sum but Get & Transform doesn’t treat […]
Kevin Lehrbass and I went head-to-head and came up with 2 solutions for 1 problem: how can Excel alphabetize a list of titles and ignore: A, An and The? Kevin uses a sweet array formula while I went the guerrilla route and implement a manual step. We both agree that any solution has to consider […]
Continuing from last week’s FunkyFunktionFriday, we expand on the Full Outer Join and I show you how to write IF statements in the PowerQuery (Get & Transform) editor. You’ll see how to add a custom column, do more data shaping and boost your overall familiarity with the gifts that continue to flow from PowerQuery. My […]
Today’s FunkyFunktionFriday continues from last week and explores the joins in Excel’s Power Query (Get & Transform). We start with a list of people who were invited to a party, and a list of people who are coming and the food they’re bringing. It would be great to have a single list that not only […]
Both George Mount and Oz du Soleil have spent time as Commissions Analysts. Today, George and Oz discuss ways of handling negative numbers since Payroll can’t process a check for a negative amount. How do George and Oz address this in their Excel models? And what are other challenges that face real-world Commissions Analysts? The […]
This Funky Funktion Friday we look at the Anti Join in Excel’s Get & Transform (aka Power Query). The mission: matching songs & artists in an old list to find out what’s still missing on the new list. Thus, we do an anti-join to eliminate matches. The twist: a lot of songs have the same […]
Here’s the problem: WyattD KWilliam UBrian We need to separate the First Name and Last Initial but, for some names, the Last Initial is out front. So, we need to turn these into: Wyatt D William K Brian U Solution: CODE and Flash Fill My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My blog: http://datascopic.net/blog2-2
Here’s my raw accent. The one that I try to cover over in my Excel tutorials. I recently discovered Accent Tag videos and decided to do one. I’m often asked about my accent and I don’t know where it came from. Most of my life has been spent in Chicago, but I’m asked if I’m […]
SSSVEDA 2016 has been great. A lot of good content was created. I’ve appreciated the comments, support and feedback. So, here’s a video thanking y’all, and sharing lessons I learned. LESSONS – A project like this reveals what a person gives a damn about. Being committed to something like this isn’t something you can phone […]