Excel Data Modeling - Stop Using XLOOKUP

Excel Data Modeling - Stop Using XLOOKUP | Learn PowerPivot

Рет қаралды 77,467

Күн бұрын

Ok, so the boss wants you to connect lists in excel and is going to ask questions about the data. Most people would us complicated XLOOKUP, or VLOOKUP formulas to solve this. But let's see if there is a better way...
⌚*TIMESTAMPS*
0:00 Boss says connect these lists
0:26 Intro to Data Modeling and PowerPivot
1:03 Sample Data Walkthrough
2:31 Importing Data into PowerPivot
3:32 Understanding Table Relationships
4:19 Connecting lists (tables) in PowerPivot
5:53 Building Pivot Tables using the Data Model
6:55 Editing the Data Model
7:21 Creating Simple Calculations in PowerPivot
8:28 Bonus -- Adding a Second Pivot Table Using the Data Model
9:08 Connecting Multiple Files in PowerPivot
💾*SAMPLE DATA DOWNLOAD*
1drv.ms/u/c/a6a54ea252cde109/...
📢* FREE RESOURCES*
Mega Excel Keyboard Shortcut List
view.flodesk.com/pages/63bf31...
Quick Reference Guide
view.flodesk.com/pages/63bf2f...
☕*BUY ME A COFFEE?*
www.buymeacoffee.com/professo...

Пікірлер: 69

@user-rm6sp4db3o 4 ай бұрын

Hello! Beautiful video! I started using Power Pivot about 7 years ago and soon discovered that the best way to get the data inside the Data Model is through Power Query and not directly from Power Pivot GUI (a lot more sources available, huge ETL capabilities, etc). In fact I would say Power Query was built as main ETL for Power Pivot. I know that complexify the beginner's life (some M language over and above the DAX needed in Power Pivot), but the results are simply amazing. And the bonus is that whatever one learns about Power Query and Power Pivot is 100% applicable in Power BI, if ever he/she wants to move into that BI field.

@ProfessorCubs 4 ай бұрын

Hi, and thank you for the kind words! 😁 And you are on point with your comment; especially how you develop transferrable knowledge between the three apps. 👍 Be great!

@5pctLowBattery 3 ай бұрын

Do you have any recommendations on videos that helped you the most learn power query?

@Iron-and-strings 4 ай бұрын

Very well done video, I subscribed and look forward to watching more!

@ProfessorCubs 4 ай бұрын

Thank you very much. 😁

@nandagopalsura 4 ай бұрын

Hi please make full video on power pivot and power query

@ClayNico 4 ай бұрын

Great job on the video, really easy to follow...

@ProfessorCubs 4 ай бұрын

Thank you very much!

@surajbhardwaj428 4 ай бұрын

Wow... What a great way to teach...Like it😊

@ProfessorCubs 4 ай бұрын

Thank you very much! Be great!!

@Seftehandle 4 ай бұрын

I loved the video. As I am hooked up on xlookup and wary of power pivot, it completely transform me to become fan of power pivot as well.

@ProfessorCubs 4 ай бұрын

Thank you, and glad you were able to get some value from it. 😄 Be great!

@Seftehandle 4 ай бұрын

@@ProfessorCubs well I am still not convinced that it would be enough to replace xlookup, since ot takes out more memory, but for model references, sure, it could be used

@Ready_Set_Boom 3 ай бұрын

Always had issue with data models especially in cases where a column gets renamed or when an invalid data type appears in a column.

@ProfessorCubs 3 ай бұрын

@@Ready_Set_Boom I feel ya... they tend to have a very delicate balance and the slightest changes can make for a bad day. ☹ 😂

@Ready_Set_Boom 3 ай бұрын

@@ProfessorCubsi found that pulling the tables into power query then using that output for pivots make it much more adaptable to changes.

@ExcelTutorials1 4 ай бұрын

This is awesome!!

@ProfessorCubs 4 ай бұрын

Thank you! Be great!

@dibyajyotigoswami1517 4 ай бұрын

Great work❤

@ProfessorCubs 4 ай бұрын

Thank you very much!

@wattsbrett 2 ай бұрын

Great video, thank you!

@ProfessorCubs 2 ай бұрын

Thanks! And glad you liked it!

@AshokSankarVarmaChekuri 4 ай бұрын

One issue we encounter with Power Pivot is its lack of compatibility with Mac. Consequently, any slicers created using Power Pivot will only display as images for Mac users, which can be frustrating.😕

@ProfessorCubs 4 ай бұрын

I hear ya, and that really stinks. 🙁 Perhaps one day they'll get it together? 😄

@krishnatate4765 3 ай бұрын

Helpful 🙂

@ProfessorCubs 3 ай бұрын

Glad you were able to get some value from it. 😁

@abdulhamidalhaddadi2255 4 ай бұрын

Thank you

@ProfessorCubs 4 ай бұрын

You're welcome! 😁

@daillengineer 4 ай бұрын

awesome vide! what do you use for your animation?

@ProfessorCubs 4 ай бұрын

Thanks, and currently using D-ID for the animation.

@Silverwolph 4 ай бұрын

Love the video, but in some case wouldn't you rather use dynamic arrays as they auto refresh?

@ProfessorCubs 4 ай бұрын

Sure, that works too! This is a case of classic excel- many ways to do the same thing. 😀

@CairgyMmapetla 2 ай бұрын

A like and a subscription! 😊

@ProfessorCubs 2 ай бұрын

Thank you very much! 😄

@wiratbolli.3147 3 ай бұрын

Create 1 series of sum product which include everything for sum product n cell referencing cz YT don't have good contain on it..please

@Junmarvi 4 ай бұрын

Count me in..:)

@matthewfigueroa846 3 ай бұрын

Great video. How would you make a relationship using dates?

@ProfessorCubs 3 ай бұрын

Hmm... good question. Never had to do that. Can you give a little more information about what you're working with?

@matthewfigueroa846 3 ай бұрын

@@ProfessorCubs Thank you for responding! run reports for a call center and I created a Dashboard. However, i have to use 2 different reports that only share the dates. I would like to use 1 slicer to apply to my dashboard to control the information from both reports. Hope that makes sense. Sorry.

@ProfessorCubs 3 ай бұрын

Hmm, ok... given what you said, and without seeing your data, try right clicking on the slicer and look for 'Report Connections', from there you'll see a list of sources you can connect to. Not sure how your reports are formatted, so you might need to make them into Pivot Tables. Pardon the cheap plug... here is a video that shows you how it works- kzfaq.info/get/bejne/kNNohLyC3N-2iZs.html

@matthewfigueroa846 3 ай бұрын

@@ProfessorCubs Thank you sir!

@ProfessorCubs 3 ай бұрын

You're welcome. 😁

@jeremiahcelestinomonroy9184 4 ай бұрын

I've seen so ma vids about this, but I find it too long and complicated but your seem simple and straightforward but also shrot

@ProfessorCubs 4 ай бұрын

Thank you very much, glad you found the video useful. 😁

@pascaljoly5752 4 ай бұрын

instead of having to pick one file then the next file, can you pick from a folder so that you only need to pick the folder that contains all the files you need?

@ProfessorCubs 4 ай бұрын

Hi, off the top of my head, I don't believe you're able since PowerPivot needs to know the individual files you want to use. 😁

@pascaljoly5752 4 ай бұрын

@@ProfessorCubs thank you

@ProfessorCubs 4 ай бұрын

You're quite welcome 😁

@inearplays 4 ай бұрын

The title is misleading. I don't think you have to stop using Xlookup. Use both depending on what you need. Xlookup is designed to lookup quickly and cell specific. If I have existing sheet with already many dependents formula, I will use xlookup to get additional data. PowerPivot can't do that because I have to transform both sheets. Some user also use lookup for validation. Example if sheet1 have all the Product name in sheet2 then you use lookup to validate. Very quick and easy. Powerpivot is designed for most likely automation or analyzing large dataset. I know most users don't know or use Power Query, PowerPivot, Data Modeling, Measure, Relationship in excel. Learn and use both.

@ProfessorCubs 4 ай бұрын

Hi! And thanks for the comments. 😀 You mentioned some good points about how Xlookup is often used. Yet you'd be surprised what I've seen people do when connecting lists, so the goal of the video was to get them into a different mode of thinking and possibly get familiar with PowerPivot, Power Query, etc. 😀 Be great!

@fajarwp8148 3 ай бұрын

Yes certainly.

@calvinwong7615 3 ай бұрын

any exercise file download for self practice

@ProfessorCubs 3 ай бұрын

Yes... in the video description area. Look for Sample Data Download 😁

@republikadugave420 3 ай бұрын

Only thing is ...i would import data via power query...

@ProfessorCubs 3 ай бұрын

That works as well. 😁

@Mel_Chant 3 ай бұрын

Is this applicable in online excel?

@ProfessorCubs 3 ай бұрын

Hi, and I don't think so. Power Pivot is only available for the desktop versions best I can tell. 😔

@scotolivera8207 4 ай бұрын

It is a good video, but there is still a lot more to explore and demonstrate. This is just the tip of the iceberg. It would be nice to create a video based on a real-life example and generate a report based on real-life data

@ProfessorCubs 4 ай бұрын

Hi, thanks for the comment. And I agree, Power Pivot can get pretty deep, pretty quick. The goal here was to get people to view thing a little differently. 😀 And I do plan on making some more Power Pivot videos in the future. 😁 Be great!

@Egyptian_Koppite 3 ай бұрын

OK, Power Pivot is cool, but how is this relevant to XLOOKUP?

@ProfessorCubs 3 ай бұрын

Hi... sometimes people will use VLOOKUP, MATCH/ INDEX or XLOOKUP to combine lists. Power Pivot is an alternative method to use.... plus knowing Power Pivot is a good feather to have in your cap 😁