Excel Data Modeling - Stop Using XLOOKUP | Learn PowerPivot

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*

📢* FREE RESOURCES*

Mega Excel Keyboard Shortcut List

Quick Reference Guide

☕*BUY ME A COFFEE?*

[inearplays]

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.

[Octavian-kc]

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.

[AshokSankarVarmaChekuri]

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

[nandagopalsura]

Hi please make full video on power pivot and power query

[republikadugave]

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

[wiratbolli.]

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

[labmeeting]

Forgot a step. Step 1: don't buy a Mac. Step 1a: reply all - parallels isn't a solution, it's an apology.

[jitesh]

Hi sir, beautiful video but i have a doubt after creating the relationship between the sheets . Let say here on the above example i want to show both the sheets in a single sheet. Like where ever customer name is present it must show his name, his orders etc.. who to do this without pivot table?

[Iron-and-strings]

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

[surajbhardwaj]

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

[Seftehandle]

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.

[Egyptian_Koppite]

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

[jeremiahcelestinomonroy]

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

[Sdfsoepvmsywocmzyw]

Power pivot? lol. Index Match is the king

[scotolivera]

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

[Silverwolph]

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

[calvinwong]

any exercise file download for self practice

[daillengineer]

awesome vide! what do you use for your animation?

[pascaljoly]

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?

[ClayNico]

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