The New Geography Data Type in Excel: Let’s Play With It

/in /by

Data Types were recently release for certain Insiders and in this video I play with the Geography Data Type to show you some strengths and weaknesses.

Some things we see:
– We can’t copy-paste-as-values to get rid of the Data Type and keep the value
– The Geography Data Type tries to fix things for us; e.g., it converted “Zion, IL” into just plain “Zion.”
– We can get city names from a zip code … mostly. Some zip codes returned simply “Postal Code in the United States. But if you have to look up 10 instead of 200, hey! There’s a lot to appreciate.

Finally, I look at what happens if I open a file that has Data Types, but the version of Excel doesn’t have Data Types?

Check it out!

For an intro to 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

Import from a Specific Folder with Power Query … and that Formula.Firewall Error

/in /by

Importing data from a folder in Excel’s Power Query is easy. But what if you want to use a dropdown list to select and import from a specific folder? It involves passing a parameter (as we’ve done in previous videos) and a little M code.

This video shows 2 methods and addresses the mysterious error:
Formula.Firewall: Please Rebuild This Data Combination

The first method involves telling Power Query to ignore privacy levels.

The second method shows how to respect the privacy levels and still get Power Query to cooperate. There’s some M coding involved but not too much.

Thanks to the following people for their help:
Imke Feldmann, https://twitter.com/TheBIccountant
Wyn Hopkins https://twitter.com/wynhopkins
Ken Puls https://www.excelguru.ca/blog/

Ken Puls’ blogpost on the Formula.Firewall error:

For an intro to Power Query here’s 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 Parameters in Power Query to Generate Random Data + Data Validation

/in /by

Let’s simplify the passing of parameters in Power Query, and use it to create a list of random names. In this video we use the Drill Down feature in Power Query so that we can ask for x number of names.

But we go further! When if we have only 50 names but need 60? Something needs to tell us we don’t have that many. So! This video shows how to use Excel’s Data Validation, COUNTA and a Named Range to prevent requesting what’s not available.

Check out the video. Share your thoughts and ask your questions!

For an intro to Get & Transform (Power Query) try my Lynda.com course:

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
Website: https://ozdusoleil.com

My old blog: http://datascopic.net/blog-2-2

Passing a Parameter in Power Query: Find reviews that need to be done in x days

/in /by

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 Query.

It requires a little M-coding. A little!

Passing a parameter or variable into Power Query seemed like a strange idea. Why not just use a filter? That was my thought until I came across an odd situation:

We need to import data from specific files, but never all at once. But it’s a hassle to navigate from one file to another. It’s better to go ahead and import all the data and then pass a variable to Power Query and let Power Query filter what we need and don’t need.

Excel on Fire shirts and mugs:
https://www.zazzle.com/excelonfire
For an intro to 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

VLOOKUP-True Equivalent in Power Query with Dynamic Tiers

/in /by

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 a non-M-code, rogue way to get Power Query to be responsive when changes are made on the worksheet.

For Excel on Fire swag: https://www.zazzle.com/excelonfire

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

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