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

Dynamic Arrays | FILTER | Retrieve Only Specified Columns

/in /by

The new FILTER function, as part of the new Dynamic Arrays in Excel, is fantastic. However, it is set to filter and retrieve data from contiguous datasets. This is a problem when, for example, a person needs 5 columns from a data-dump of 30 columns.

This video shows how to use the CHOOSE function to both, FILTER and retrieve specified columns–only those columns that you want.

Download the workbook: https://datascopic.net/FNA

 #DynamicArrays
#FILTER
#CHOOSE

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

Data Literacy, Power Query, Understanding Data and Reports

/in /by

This video is about using Excel in the real world. Here, I was faced with a situation where a new way of paying commissions was created, but a new report for me to use wasn’t also created. Instead, an existing report was tweaked and it seemed good enough until someone asked a question and requested more transparency. OOPS!

And this is where data literacy comes in.

To figure out the problem, it was necessary to use Excel in a supporting role to dig into questions like:
– What was this report really meant for?
– What are the definitions in the report?
– What are the triggers in the report?
– Do the calculations accurately reflect what’s intended and what’s in the compensation plan?
– What are weaknesses in the report?
– Where can I turn for transparency?

In situations like these, it’s not ok to blindly rely on a report. When there are questions, dig in.

This video also considers how I might have handled this problem if I had Power Query at the time. You’ll see:
– Left outer join
– Power Query’s Replace Values
– Remove columns
– Filter
– Reference 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

Investigating Data with Bar Graphs and Pivot Tables

/in /by

There are a couple of reasons for this video.
1. Often, to be good with data, the technical Excel tips & tricks aren’t necessary. Sometimes we have to think, probe and strategize.
2. This follows on my previous video that showed how graphing the data unlocked a mystery. Here again, is a true situation where it helped to make a pivot table and trow the data into a bar chart.

The video also uses Tables, and the WEEKNUM function to get to the bottom of the mystery.

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
#WEEKNUM
#PivotTable
#ColumnChart

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel SLOPE Function and Scatter Plot for a Pricing Formula

/in /by

This video takes us back to my very first paid project in Excel. A photographer had a problem calculating the price of a finished and framed photo. The main part of the problem was the mystery around the price of the frame.

We gathered receipts and I figured, “what the heck, let’s plot the known data on a grid.”
The Excel scatter plot showed a straight line. That made it easy to figure out the frame shop’s pricing formula.

The equation of a straight line: y=mx+b

 
y = final price
m = slope * length * width + b
EASY!!!

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

IDEAS in Excel: Be wary of these weaknesses

/in /by

Excel has a feature called Ideas. It offers to provide information about the user’s dataset, and accepts natural language questions. This video shows areas where you need to watch out or you can get into deep trouble.

I’m skeptical of AI/ML (artificial intelligence and machine learning). Why? Because the hype and marketing around this technology talks about the promises and what’s possible. But this video shows several examples where Ideas (using AI/ML) makes goofs.

Ideas–like any other AI/ML application–makes guesses. Some guesses are much better than others. Some guesses are way off the mark and if we have to be extra careful about results that we didn’t generate ourselves.

Yes, AI/ML can be helpful. But it puts more responsibility on the user to understand and verify everything around their data.

#ExcelIdeas
#Ideas
#ArtificialIntelligence

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