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 […]
As follow up to my previous video on making running totals in Excel’s Power Query (Get and Transform) I show a much simpler solution. Thank you to Wyn Hopkins and Peter Bartholomew. Special thanks to Gráinne Duggan for an important warning. If we make the running total, sort the source data and then refresh, the […]
This video is a wild one! To make running totals in native Excel is pretty easy. However, in this video, you will see the adventure for making Running Totals in Power Query (Get and Transform). You will see some M Coding in Power Query, Lists, List.Range, List.Sum, custom columns, and putting an entire table inside […]
I maintain a file of dummy data and I describe the 2 reasons in this video. But where does the data come from? Here, I show how to import and clean data from this web page of 200 top Brazilian first names. http://www.studentsoftheworld.info/penpals/stats.php3?Pays=BRA Once the names are imported, the names have to be fixed using […]
Download the workbook here: http://datascopic.net/GTInstinct LET’S WHOOP CRAP DATA! This 5-question quiz is designed to expose you to Crap Data and test your instinct for what Power Query (Get and Transform) features you’ll need for victory. The solutions aren’t in this video because we’re focused on INSTINCT–what you think you’ll need before you get started. […]