Entries by Oz du Soleil

Octopus Spreadsheets, Asking for Help and Conceptual Thinking

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, […]

Replace Each x in a Matrix With Column Headers in PowerQuery

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 […]

Excel Weekend 4 – Summary with Cristiano Galvão and Oz du Soleil

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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My […]

Connect Power Query to a Dynamic Dropdown List

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 & […]

Excel Data-Cleansing: When Data CanNOT be Cleansed

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 […]

Running Totals in Excel’s Power Query: the easy way

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 […]

The Madness of How to Make Running Totals in Power Query

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 […]