It’s easy to split columns by delimiter but there’s a problem if you need to automate the parsing. Get & Transform (Power Query) hard-codes a number the first time you create the query, and if that number is exceeded, new data will just disappear. This video shows how to set up a query so that […]
Here it is! My Paqui One-Chip Challenge video. This chip is HOT! You’ll see. #onechipchallenge #sweeps Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
This video responds to a question: how can you add a line-feed between every 30th character. This was a bit intimidating as I wondered WHY would someone’s data be in such a condition? But I thought of an example where area codes might be all smashed together, with no other way to split them apart […]
Pants on Fire is a storytelling event in Portland, OR where the audience hears seven outrageous stories, and one is a lie. Here, I tell the true story of playing cards on a city bus with spree killer Alton Coleman. Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
This video explores Aggregating Joins. These are strange because they can be handy. However, as we see in the video, sometimes Grouping or regular joins are better solutions. Still, you should know that Aggregating Joins is available to you. I’m interesting in knowing your thoughts on this. Do you use Aggregate Joins a lot? Whats’t […]
My past few videos have covered Joins and other methods for merging data in Microsoft Excel using Get & Transform (Power Query). Now, take this 10-question quiz. Test your knowledge on working with Joins. See if you’re ready to battle Crap Data and help get this world’s data clean. Website: https://ozdusoleil.com My book: Guerrilla Data […]
This came from Leila Gharani Original Video https://youtu.be/OJLfPc9YlqE Then Kevin Lehrbass took it on: The challenge: You have data in a matrix. Locate a specific piece of data and retrieve the name of the column it’s in. This is tougher than you might think. This video is a glimpse of the solutions from Leila (COLUMN, […]
So, you’ve got a bunch of data all in one cell. You need to not only get it out, but it needs to be in rows, stacked in a single column. With this new Get & Transform (Power Query) feature it’s EASY! Split Column by Delimiter into Rows This video has 2 examples: 1. A […]
In this video we’re measuring student progress against program requirement. A full outer join is used as a final step. However, the cross join is needed so that we can identify where students haven’t done ANY work in a category. The full outer join won’t pick up data that doesn’t exist. What is a Cross […]