PowerPivot Relationships are EASIER than VLOOKUP, not just faster

Подписаться 16 тыс.

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

50% 1

NEVER need a VLOOKUP again! PowerPivot relationships are easier and faster to create in Excel. www.PowerPivotP...
VLOOKUP is used for merging or blending two tables of data in Excel. It's the original mashup tool! But it's a scary function with arguments like "lookup_value" and "table_array" and my personal favorite "[range_lookup]" which really should be named "[always_set_me_to_FALSE]".
It turns out that VLOOKUP is harder for some people to use than pivots themselves! I've long viewed PowerPivot's relationships capability as a far superior alternative to VLOOKUP, but purely from a speed and elegance standpoint.
It turns out that PowerPivot is also a much friendlier and more approachable alternative as well.

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

3 окт 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@sharifalhumaid8537 7 лет назад

Awesome. Thank you for providing a simpler formula than relatively complicated lookupvalue.

@samzhou1394 6 лет назад

Hello To whomever created this video. I'm also a heavy user on pivottable to model extremely complicated financial products and derivatives. Even though this is a video from 5 years ago, I somehow found it the first one that actually make me start to think that powerpivot is useful and more efficient. Thanks for the example.

@obits3 6 лет назад

I would also like to add that lookup formulas and sumifs are better than these “power” tools for one reason: no refresh needed. If I change the core data, the formulas are automatically updated. This is great for building large workbooks of schedules that all must relate to each other for work papers.

@craigconway9930 9 лет назад

Just an FYI, insert the range into a defined (named table) first, then all you have to do is type the name of the table in the vlookup. It also lets you type the name of the column you want rather than using the column number. Its overall just a better method. Typically faster if you have to do more than one vlookup. Probably the same time if you're just doing one vlookup.

@Corey_Bee 8 лет назад

+Craig Conway vlookup is never the right answer

@dapperlink 3 года назад

I love and agree with your learning graph, but I think I would’ve slipped index matching between lookup and pivot tables. For some reason, IM freed me from the inflexibility of vlookup and forcing the lookup array always in the leftmost position. I went nuts with using it, even though the formula is no less scarier when you look at it. Thank you for the Power Pivot approach. Can’t wait to try it out!

@dilipdnair 4 года назад

That helped! 👍🏻 Btw, have seen that excel progression picture on a power pivot book by Rob Collie and Avichal Singh.

@BZaildar 10 лет назад

This helped me a lot Thank you! Bought your book, Loving it. Kind of book you just want to read in one sitting! Thanks a lot again.

@mattj4513 9 лет назад

I'm at the VLOOKUP stage in my Excel continuum and starting to learn Pivot Tables. I do have to say if you use named tables in Excel, then the VLOOKUP() function becomes a lot more readable and faster to program than using Pivot Tables

@Corey_Bee 8 лет назад

+Matt J Skip VLOOKUP and learn INDEX+MATCH.

@JohnJohnson-ms4qz 7 лет назад

FALSE looks for exact values within a table. if you are trying to return data for a value within a range on a table. a simple example is grades: if you have a table where C is all scores above 70, B is all scores above 80, A is all scores above 90, and you are trying to find what grade to give for 85, TRUE will look for the score which corresponds with the highest grade not exceeding the next grade's lowest score, ie excel will look at 70 < 85 and hold C, then it will look at the next 80 < 85 and hold B, then it will look at the next 90 > 85 and reject A, outputting B as your grade for an 85 score. in this example, if you had used FALSE you would get #N/A because 85 isn't a value in your table. just a little nugget for your FYI

@mattmatt245 10 лет назад

Can you explain two things : 1) How do you do approximate match with Related function (which is default for vlookup) 2) How do you do 3-way lookup with RELATED, when you need to choose from multiple tables (possible by combining choose and vlookup)

@jaimeguzman6005 2 года назад

So either write several vlookup formulas or write several related formulas. This after turning the data into tables. Did you have to establish a relationship between the two tables? While Power Query is more efficient, this wasn't the best option for what you were trying to accomplish.

@obits3 6 лет назад

Pivot tables are easier than VLOOKUP because VLOOKUP often breaks unless you add IFERROR. Thus, a useful VLOOKUP function will actually look something like =IFERROR(VLOOKUP(A1,D1:E34,2,FALSE),0). That’s downright scary for a novice Excel user.

@joefromdc 7 лет назад

to me vlookup helps me to understand the relational aspect of power pivot. i think that was the point of ur video. before u learn how to vlook up thru table names, u had to go thru the process of highlighting the whole dam table. its a step forward. i agree with ur chart

@olegg2663 11 лет назад

Great Video! Could simplify even further by not getting into RELATED() syntax and simply dropping the fields onto a pivot table to show the real "magic" of PowerPivot

@priyajoby2605 7 лет назад

VLookup is straight forward and very powerful to use. I dont understand what is the big deal with Vlookup.. This video can be much shortened by coming to the point straight away.. Was quite dragging until 6:58

@p3adaptive 11 лет назад

Well in general, you always have duplicate values in ONE of the two tables. But if you have dupes in BOTH, you have a problem. One of the two tables has to be "unique." Oftentimes, you have blank cells, and even 2 blanks count as dupes.

@MsTalal21 5 лет назад

I have question, I,m using PowerPivot to also import large data from access, but I have noticed that my relationship is not clickable in PowerPivot? Like I see it as an option but I can't click it or use it? Can someone Help me with that??

@ThePowerUser 11 лет назад

Great video! I see you used boxofficemojo data. Admissions vs Gross Box Office... =) Quick fact: in most countries the #1 movie of All Times for Box Office is The Avengers (2012) but Titanic continues to be the #1 in admissions (because the 3D version added to the previous release)

@EvansAdam100 5 лет назад

What a long video for such small content delivery

@edisondominguez652 5 лет назад

you don't know fast forward?

@theharoonmsk 2 года назад

Thank you, it helped me

@Slagroomtaart79 3 года назад

This function is weirdly missing for my in my Excel. I have it Dutch; it should be named GERELATEERD -but it's simply not there

@ErickHuonReis 10 лет назад

The problem is: Vlookup gives us the first entry when multiple exists, which can be good. But PowerPivot demands unique relationships. Example: (Year) Employee ID vs Employee Name in one table and on the other, only Employee ID, and I want to get the Employee Name, so I would use vlookup. Some people might have got married and changed their (last) names, but I don't care, any of them is ok. PowerPivot Related and Lookupvalue DAX formulas won't work because the same Employee ID appears for two different names (single and married). Any ideas how to make it work?

@edisondominguez652 5 лет назад

Once I connect this columns with Related via Power pivot, is there an automatic refresh If I add new information at the bottom of those lists/columns?

@FabricioAyres86 9 лет назад

I would like to understand how you got to "gone with the wind" result.

@skeeterburke 7 лет назад

wow thanks this power pivot nonsense is starting to make sense. I'm an excel formula and vlookup whiz! but yeah its a lot of work. btw if you do still rely on vlookup you should #1 create a named range for the lookup table #2 put the name of the lookup table right above it and #3 highlight the lookup table purple or orange or whatever, and if you do all that, its way easier to use your lookup table over and over again, it kinda sticks in your head like a table of all the Sundays in 2017, call it wkends2017

@chuiyanmo 5 лет назад

Couldn't agree more on the excel path picture hahah

@skeeterburke 7 лет назад

you kinda remind me of derek banas, very simple and straightforward presentation

@lindameredith1941 5 лет назад

Why not use the wizard?

@martineavila5708 4 года назад

Very good.

@lomidzedze 7 лет назад

How can i export vlookup-ed values in the excel with changes.?

@realScottThomas 6 лет назад

Actually I learned pivot tables pretty early on. I learned index match match and power pivot before vlookup. I still have yet to learn it :3 lol.

@JustSomeGuy009 9 лет назад

You should create ACTUAL tables instead of the ranges as shown in your video. Read up on structured references. Tables with appropriate table and column names makes everything much easier to write, read, and remember.

@JustSomeGuy009 9 лет назад

Also, powerpivot relationships require at least 1 column with unique data (like with actual databases, ie. Oracle, MySQL, Access, etc.). Vlookup has no such requirement, although the data you get might not be what you wanted. It is an important thing to know when trying to build relationships with powerpivot.

@GaryMillerUK 3 года назад

@@JustSomeGuy009 " the data you get might not be what you wanted" is quite a downside🤔

@VanessaKlinger 7 лет назад

That was great! Thank you!

@neversayjello 6 лет назад

amazingly I never used charts, but I know array formulas.

@monicacisneros327 3 года назад

new viewers skip to 5:50 minutes to see the meat of the video.

@rauljimenez5485 5 лет назад

Please share the files so we can practice along!! Thank you that is a great video!!

@SyedMuzammilMahasanShahi 6 лет назад

Thanks for this wonderful video

@monicacisneros327 3 года назад

A lot of fluff to get to the answer.

@ajayshakti5818 5 лет назад

Ms access easy than power query..

@VanessaKlinger 7 лет назад

I found pivot tables far easier tables then vlookups

@antonporteus 10 лет назад

Seriously? There's nothing difficult about a VLOOKUP!

@Corey_Bee 9 лет назад

Not everyone has the same skill level. Vlookup is actually a pretty terrible function for other reasons though.

@Corey_Bee 8 лет назад

+dhsilv2 Not saying it's easier to use PowerPivot for simple examples. But VLOOKUP is literally never the right answer. If you're learning Excel, it doesn't help you to learn crappy formulas just because they're a little easier. If you have a simple need that doesn't require table relationships, you should use INDEX/MATCH instead.

@sharifalhumaid8537 7 лет назад

The function is OK, and I agree with you in small data set. But as the data grows into hundreds of thousands, vlookup function slows down and may crash Excel. In such situation, harnessing databases (linked tables) and powerpivots is the ideal solution.

@skeeterburke 7 лет назад

until you have to do 4 or 5 to get the result you want, I see his point. after all isn't that what we have computers for, to do all our work? I love vlookup and I love seeing the aha moment on peoples faces when you explain it in simple terms for them, its like, glad and sad, I'm happy I have this tool, but I'm angry I never knew how to use it till now

@ryanshannon7703 6 лет назад

I think the strongest use case for Power Pivot is that you're creating a data model. THIS is probably THE reason why power pivot is better than the vLookup function. How many rows are you able to calculate when hitting a data cube? You seriously want multiple vLookup functions hitting over 100k rows, for 5 different vlookups? Of course, you're calculating RBAR (row by agonizing row). Power pivot allows for set based calculations to be performed, without killing your machine. Also, how many rows are you limited to in an excel spreadsheet? You don't have that limitation in Power Pivot. You can have tables, (not spreadsheets) that have over 2 million rows and still perform the set based calculations you need. Whereas I know the purpose of this video is to simply introduce Power pivot, there's a lot of key information about Power Pivot that is left out that demonstrates the superiority of Power Pivot over row based calculations called by a function. This doesn't even touch on being able to use your data model as a source for Power BI portal/dashboard integration, using a data source for R statistical analysis, etc.... Also, you can have a static aggregate or calculation in the separate window pane at the bottom of the window, just below the 'table' window. You can use those values in Pivot table calculations, as well as use your power pivot table as a make-shift 'data cube' source (if anyone has ever worked with an SSAS data source), to apply further aggregates, slicers, etc...