Octopus Spreadsheets, Asking for Help and Conceptual Thinking

/in /by

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, complex workflows and data spread across multiple sources.

Otherwise, we’re trying to wrestle an octopus. And for those of you who’ve have that misfortune, you know that wrestling an octopus is miserable.

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 old blog: http://datascopic.net/blog-2-2

Replace Each x in a Matrix With Column Headers in PowerQuery

/in /by

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 sexy. It requires a full outer join, Unpivot, Pivot – Don’t Aggregate. But first, we have to break the original data into 4 pieces. Check it out!

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 old blog: http://datascopic.net/blog-2-2

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

/in /by

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 old blog: http://datascopic.net/blog-2-2

Using Power Query to Keep Duplicate Records and Delete Single Records

/in /by

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 barside at Cascade Brewing Barrel House in Portland, OR. So, grab yourself a beer and enjoy the video.

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 old blog: http://datascopic.net/blog-2-2

How to Import Excel Files with Power Query; BUT there’s a PROBLEM

/in /by

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 confrontation. Tell the person to ” knock it off!” Another move is to modify the query.

Check out the video. Please comment. Share your thoughts. Ask questions.

The song in the video is “Quintal do Céu” by Seu Jorge

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 old blog: http://datascopic.net/blog-2-2

Connect Power Query to a Dynamic Dropdown List

/in /by

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 & Transform (Power Query) try my Lynda.com course:
https://www.lynda.com/Excel-tutorials/Excel-2016-Get-Transform/608994-2.html
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

Excel Data-Cleansing: When Data CanNOT be Cleansed

/in /by

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 weren’t just formatting issues. One date had a year “20165.” So many red flags piled up and this is when the responsible thing to so is STOP! This data cannot be trusted. Send the data back to the source for verification and validation.

BTW: you’ll also see use of the Locale feature in Power Query (Get and Transform).

Join me at Patreon: https://www.patreon.com/Data

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 old blog: http://datascopic.net/blog-2-2

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

/in /by

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 running total will turn into a mess.

My Patreon Page: https://www.patreon.com/Data

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 old blog: http://datascopic.net/blog-2-2

The Madness of How to Make Running Totals in Power Query

/in /by

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 a single column.

In Excel we can work at the cell level and that’s what makes running totals easy. But, in Power Query, we have to work on entire rows or columns. So, there are a lot of steps to force Power Query to give us what we want.

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 old blog: http://datascopic.net/blog-2-2