In this video we want to look up whose annual review is due within x days. Quickly list who’s due in 30 days, 14 days, 90 days … To make this easy–especially if this Excel workbook is for someone unfamiliar with Excel–we’ll set things up to “pass a variable” or “pass a parameter” into Power […]
So, we’ve got a discount grid and need to assign the right price to various amounts. Then, you want to add tiers or change the levels. VLOOKUP-True is what you’d use in native Excel. However, in Power Query (Get & Transform) this is very messy. Research revealed intense M-code solutions. In this video I show […]
When asking for help with a spreadsheet, it’s best to distill the need down to concepts. It might not be the real work that you need help with, or even the real data. However, by distilling a challenge down to the fundamental concepts and building nonsensical models, we can see our way past technical jargon, […]
Imagine a grid that has students and subjects. In the Algebra column, Eric has an x. How do we replace the x in the column to show ‘Algebra’? While I was at ExcelWeekend4 someone asked how he could replace each x in a matrix with its respective column header. The solution is tough but it’s […]
ExcelWeekend 4 was a blast! Here, the founder, Cristiano Galvão and I review the event, and you get to see pictures from the event and my visit in São Paulo, Brazil. For an intro to Get & Transform (Power Query) try my Lynda.com course: Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My […]
It’s easy to get rid of duplicate records with Excel’s Power Query (Get and Transform) BUT … what if you want to do the opposite: keep duplicate/multiple records and delete the single-entries? There are a few steps. We need to use Group By, Filter, Reference a Query, and an Outer Join. This video was recorded […]
Here is a how-to for importing Excel files from a folder using Power Query (Get and Transform). It’s fairly easy to do. HOWEVER, in this example, I’m sharing a folder with someone who starts putting goofy stuff into the folder and they break the Power Query query and the process. Now what? One move is […]
Can Excel’s Power Query (Get and Transform) be used as contents for a dropdown list in Excel? And what would be a good use for it? That came across my mind recently and this video is the result of that inquiry. You’ll see COUNTIFS, Dropdown boxes, Fill-Down, and everything! For an intro to Get & […]
This is Crap data at its worst–a dataset that cannot be cleansed. This video is a dose of Excel data-cleansing reality. Someone asked for help cleansing a column of dates so they could be sorted. As I explored the data in Excel and Power Query to see what was wrong, I uncovered deeper problems. There […]