Excel Dynamic Arrays: FILTER for Optional and Required Fields

/in /by

The new FILTER function is fantastic, but I wondered how can I make an optional field?

Sometimes we do searches and want to look at items between $10 and $100 for brand X.

We also want to look at everything between $10 and $100 regardless of the brand.

That’s what I wanted to replicate with the FILTER function: an optional filter. The straight-forward FILTER function forces us to filter for something whether we want to, or not.

My solution involves using an IF statement and smart use of the # nomenclature. Check it out!

#DynamicArrayFunctions #OptionalFilter

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel Dynamic Arrays: Unstack a Column of Records

/in /by

The classic problem: unstack a column of records in Excel.
This video shows a cool way to get this stuff un-stacked. You’ll see the FILTER Dynamic Array function, the SEQUENCE Dynamic Array Function, and even the SORTBY.

You’ll see cool uses of regular Excel functions that act like arrays. This is real fire, baby!

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Power Query: Keep all Capital Letters with Text.Select in M-Code

/in /by

Rahim Ali asked me about how to keep only capitol letters. For example:

France: F
United States: USA
Republic of China: ROC
United Arab Emirates: UAE
South Korea: SK

(In this video I use names as examples to work with.)

This can be done with Excel’s Flash Fill but that’s not a dynamic solution. For something dynamic we use Power Query and it’s a little tricky and we make clever use of Add Column from Example.

We have to go into the m-code and convert:
Text.Remove to Text.Select. And then we can have Power Query select only A thru Z

And then … a TWIST!

We’ve got a problem with the character: É

I show you one way of dealing with that!

This video also includes a summary of Excel Days 2018 in Sofia, Bulgaria where I had the pleasure to give the keynote presentation and teach a 7-hr Master Class on Excel and Power Query.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel Dynamic Arrays: 6 Examples of FILTER with Multiple Criteria

/in /by

Excel’s new Dynamic Array functions are trippy! They require a whole new way of thinking.

In this video I play with the FILTER function just to see what it does. You’ll see 6 examples of complex, multi-conditional, multiple column formulas.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

5 Dynamic Array Gotchas in Excel

/in /by

Dynamic Array Functions in Excel are so fabulous … BUT!

As with anything, there are traps, warnings and caveats. In this video I quickly cover 5 things you should know about Dynamic Array Functions and Formulas.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

#DynamicArrays #DynamicArrayWarnings #DynamicArrayFunctions

Dynamic Arrays: SORT, FILTER, and Dynamic VLOOKUP

/in /by

7 new Dynamic Array functions have been added to Excel! In this video you’ll see the FILTER and SORT functions and how 4 formulas bring back results in lots of cells!

Also:
– There’s new nomenclature using the # to reference a dynamic range.
– The new SPILL error lets you know that your result doesn’t have enough room. So, you need to move something and make room.

#DynamicArrays
#DynamicArrayFormulas
#DynamicVLOOKUP

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Pass Parameter (Drill Down) to Pick Name Format with Power Query

/in /by

There’s a lot in this video! When you get this working, you’ll be entitled to wear your underwear outside your pants like a true Super Hero!

The challenge:
We’d like random names, but also choose which format we’d like our result in:
– First Name Last Name
– Last Name, First Name
– First Name MI Last Name
– Last Name, First Name MI

I thought about using a custom function but realized it’d be simpler to drill down (pass a parameter).

In this video you see:
– Column by Example
– Excel Tables
– Power Query
– Left Outer Join
– M Code
– Merge Columns
– and EVVYTHANG!

Download the file here:
http://datascopic.net/nformats
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
#PowerQueryTutorial
#DrillDown
#OuterJoin

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

ExcelGuru Challenge #3: Parsing Text & Own Your Excel Style

/in /by

Ken Puls of ExcelGuru presented a 3rd Excel Challenge and here’s my solution.

The challenge is to take a word, assign values to each letter and generate a total for each word.

A = 1
a = 1
.
.
Z = 26
z = 26

The word ‘Attitude’ gets 100 points.

As I started thinking about a solution I started thinking too hard about it. I wanted something more crafty or sophisticated, but then I realized that I saw a path toward success and that’s what mattered.

So, this video is really about understanding your strengths and style and not apologizing. My Excel style is akin to the style of Jerome Bettis, the former runningback for the Pittsburgh Steelers. He was big and powerful … also kinda slow and not elegant.

But what’s the bottom line? The bottom line is to get the job done–however you can responsibly get the job done.

The ExcelGuru Challenge No. 3 solutions are here:

Power Query Challenge 3 Results

Jerome Bettis Highlights

#ExcelChallenge
#PowerQuery
ExcelGuruChallenge

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Download the workbook: http://datascopic.net/EGC3

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

Custom Function in Power Query to Merge 2 Tables

/in /by

Custom Functions in Excel’s Power Query. It’s finally time to take them on. For a while I couldn’t think of a good use. It’d seen the how-to but never really a what-for.

In this video I propose a common situation where lists will trickle in and we’ll need to convert the state abbreviations into state names via a master list. E.g., convert LA to Louisiana, and GU to Guam.

We’ll get one list, then 2 hours later another list, then tomorrow we’ll get 2 lists. Ongoing trickle. We could write a new VLOOKUP every time, but that would get tedious.

A Custom Function in Power Query will help us out.

I show how to do this as a partially manual process with 2 steps:
1. Manually bring the data into Power Query
2. Apply the custom function.

Pretty simple, but it takes some thinking.
You’ll see some M Code and the “Insert Step After” feature.

#PowerQueryCustomFunction
#CustomFunctionInPowerQuery
#MergeTablesInPowerQuery

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Download the workbook:
http://datascopic.net/CFMerge
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