No video :(

Excel Data Modeling - Stop Using XLOOKUP | Learn PowerPivot

Подписаться 3,4 тыс.

Просмотров 78 тыс.

50% 1

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/a6...
📢* FREE RESOURCES*
Mega Excel Keyboard Shortcut List
view.flodesk.c...
Quick Reference Guide
view.flodesk.c...
☕*BUY ME A COFFEE?*
www.buymeacoff...

Опубликовано:

17 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 69

@user-rm6sp4db3o 5 месяцев назад

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 5 месяцев назад

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 4 месяца назад

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

@nandagopalsura 5 месяцев назад

Hi please make full video on power pivot and power query

@Seftehandle 5 месяцев назад

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 5 месяцев назад

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

@Seftehandle 5 месяцев назад

@@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 4 месяца назад

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 4 месяца назад

@@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 4 месяца назад

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

@Iron-and-strings 5 месяцев назад

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

@ProfessorCubs 5 месяцев назад

Thank you very much. 😁

@surajbhardwaj428 5 месяцев назад

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

@ProfessorCubs 5 месяцев назад

Thank you very much! Be great!!

@AshokSankarVarmaChekuri 5 месяцев назад

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 5 месяцев назад

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

@ClayNico 5 месяцев назад

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

@ProfessorCubs 5 месяцев назад

Thank you very much!

@wattsbrett 3 месяца назад

Great video, thank you!

@ProfessorCubs 3 месяца назад

Thanks! And glad you liked it!

@inearplays 5 месяцев назад

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 5 месяцев назад

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 4 месяца назад

Yes certainly.

@dibyajyotigoswami1517 5 месяцев назад

Great work❤

@ProfessorCubs 5 месяцев назад

Thank you very much!

@wiratbolli.3147 4 месяца назад

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

@Silverwolph 5 месяцев назад

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

@ProfessorCubs 5 месяцев назад

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

@ExcelTutorials1 5 месяцев назад

This is awesome!!

@ProfessorCubs 5 месяцев назад

Thank you! Be great!

@republikadugave420 4 месяца назад

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

@ProfessorCubs 4 месяца назад

That works as well. 😁

@matthewfigueroa846 4 месяца назад

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

@ProfessorCubs 4 месяца назад

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

@matthewfigueroa846 4 месяца назад

@@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 4 месяца назад

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- ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Zm8QYQwyRYg.html

@matthewfigueroa846 4 месяца назад

@@ProfessorCubs Thank you sir!

@ProfessorCubs 4 месяца назад

You're welcome. 😁

@Junmarvi 5 месяцев назад

Count me in..:)

@CairgyMmapetla 3 месяца назад

A like and a subscription! 😊

@ProfessorCubs 3 месяца назад

Thank you very much! 😄

@krishnatate4765 4 месяца назад

Helpful 🙂

@ProfessorCubs 4 месяца назад

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

@abdulhamidalhaddadi2255 4 месяца назад

Thank you

@ProfessorCubs 4 месяца назад

You're welcome! 😁

@daillengineer 5 месяцев назад

awesome vide! what do you use for your animation?

@ProfessorCubs 5 месяцев назад

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

@jeremiahcelestinomonroy9184 5 месяцев назад

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 5 месяцев назад

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

@pascaljoly5752 5 месяцев назад

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 5 месяцев назад

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 5 месяцев назад

@@ProfessorCubs thank you

@ProfessorCubs 5 месяцев назад

You're quite welcome 😁

@Mel_Chant 4 месяца назад

Is this applicable in online excel?

@ProfessorCubs 4 месяца назад

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

@calvinwong7615 4 месяца назад

any exercise file download for self practice

@ProfessorCubs 4 месяца назад

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

@scotolivera8207 5 месяцев назад

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 5 месяцев назад

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!

@labmeeting 3 месяца назад

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

@Egyptian_Koppite 4 месяца назад

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

@ProfessorCubs 4 месяца назад

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 😁