Creating a Custom Data Type with Power Query; including XLOOKUP and Dynamic Arrays

/in /by

The new Custom Data Types are starting to be rolled out and they’re … exciting … interesting … spooky.

This video will be the first of several that will explore the Custom Data Types, how they can be used and where to watch for GOTCHAs.

In this video we create a basic Custom Data Type using information about specialists. We have their names, contact info, specialties, birthday and school data. Later we add in their hourly rates by using XLOOKUP.

#XLOOKUP
#CustomDataTypes
#PowerQuery

You also see the FILTER function for Dynamic Arrays. GOOD STUFF! 🎉

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Power Query: Identify & Retrieve Partial Strings of Text

/in /by

It’s one thing to identify if a partial strong of text exists in a cell. It’s another to identify it and also dig it out. That’s the challenge in this video.
1. Identify if an address contains SW, South West, NE or Northeast.
2. If one of those is found, populate what was found in the cell next to the address.

This requires thinking really slowly and planning.
The first step in this video is a cross-join. Along the way, we use a left outer join and the Power Query command: Text.Contains

It’s all here!

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel Patience | Sort birthdays by the day and month | Insert 7 Rows Between Entries

/in /by

0:00 – 2:17 Intro & Set-up
2:18 – 4:48 A dark day in Arizona
4:49 – 5:33 Sort birthdays by day and month
5:35 – 7:55 Insert 7 rows between each row

This video is primarily about patience with Excel and finding solutions. Often people will comment that I make things look so easy. In this video I take the pressure off. I reveal that a lot of solutions took a long time to find, and the 5-min tutorials mask the hard work.

So. If you take a long time finding solutions, that’s how this works. Be patient. It’s not easy.

This is demonstrated in a solution for a friend. She had over 2000 rows of data and needed to insert 7 rows between each of them. It took a half day to look for a solution. Back then I used VLOOKUP. Today I used XLOOKUP and SEQUENCE (dynamic arrays).

You’ll also see how to sort birthdays by the month and day, ignoring the year. Example:
Angelo, 12APR67
Kim, 17JAN81
Nettie, 12APR93
Pete, 1NOV88
Yukio, 29SEP96

We want to sort (ascending) so that Kim is on top because her birthday is 17JAN (earliest in the year). Pete should be on the bottom because his birthday is 1NOV.

If we were to sort the birthday column as-is, Nettie would be on top because her birthday is the most recent year, 1993. NOT WHAT WE WANT!
We use the TEXT function in order to sort and ignore the year.

#XLOOKUP
#SEQUENCE
#DynamicArrays

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel Dynamic Arrays with XLOOKUP & Form Controls (Radio Buttons and Tick Boxes)

/in /by

One way to get good with Excel is to play with it. In this video I decided to play with Dynamic Arrays and Excel’s form controls.The result is a pretty neat solution with tick-boxes and radio-buttons that allow the user to toggle a dataset between sorting ascending and descending.This video includes:
– XLOOKUP
– Tick Boxes
– Radio Buttons
– SORT
– FILTER
– IFERROR

#DynamicArrays
#XLOOKUP
#ExcelFormControls

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

DATEDIF & Dynamic Arrays | Calculating the months to complete a project | Don’t Rush Edition

/in /by

How do you calculate the number of months to complete a project? Here are problems:
Problem 1:

This project had 5 steps and each started and finished on different days …
Step1 12JAN – 19FEB
Step2 15JAN – 19JAN
Step3 30JAN – 27AUG
Step4 5JUL – 15JUL
Step5 1AUG – 11AUG

Problem 2:
A stack of multiple projects with multiple steps.

Solution involves:
Dynamic Arrays
DATEDIF
UNIQUE
MINIFS & MAXIFS

#DynamicArrays
#DATEDIF
#UNIQUE

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel: Data Models | Add to Data Model | Power Query

/in /by

After working with data in Power Query there is the option to “Add to Data Model.” What is that?
A Data Model is helpful when it’s necessary to bring multiple datasets into one. The data can be used in a single pivot table without having to physically merge the datasets into one place.

This video shows how to create a Data Model using the diagram view in Power Pivot. I also show one warning about backward 1-to-many relationships.

Download the workbooks: https://datascopic.net/DM2

#DataModel
#PowerQuery
#Excel

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil

There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Where INDEX-MATCH Wins Over XLOOKUP and VLOOKUP

/in /by

I was surprised with a situation where someone insisted that INDEX/MATCH was his only choice for a formula. I wondered “why INDEX/MATCH?” Why not VLOOKUP, XLOOKUP or a Power Query join?

When I saw his data and the restrictions that were on him, this was clearly a context where INDEX/MATCH was the wiser choice.

There are other functions and formulas that could be used to accomplish this task, but a small twist to the INDEX/MATCH function does the trick.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Beyond Excel: People Processes & Tools (and XLOOKUP)

/in /by

A lot of times what we face in the real world with data and Excel:
The Excel solution is easy but the situation is complicated

In this video I discuss the importance of People, Processes and Tools. Excel is only a tool. And a tool won’t save a situation if the process is janky (or there is no process, or the person is janky.

In this case, an XLOOKUP needed to be added to modify an existing process. But it took a long time to think through.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

#XLOOKUP

 Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

XLOOKUP & Dynamic Arrays Celebration and Demos with MVPs Wyn Hopkins and Oz du Soleil

/in /by

XLOOKUP and Dynamic Arrays are finally available for anyone on Microsoft365 (formerly known as Office 365). In this video, Wyn Hopkins and I give you demonstrations of XLOOKUP and Dynamic Arrays working together to make simple but robust solutions.

0:00 Opening
1:34 Wyn’s Demo
6:15 Oz’s Demo
10:40 Wrap-up

You’ll see it all!
XLOOKUP
FILTER
2-way lookups
SORT
TRANSPOSE
Tables

It’s one big party, y’all!

#XLOOKUP
#Dynamic_Arrays
#DynamicArrays

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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