XLOOKUP v Power Query v Power Pivot in Excel

When combining tables in Excel there are several options.

Here I show you XLOOKUP, Power Query Merge and a Power Pivot relationship.

00:00 Intro
00:43 The scenario
00:51 XLOOKUP
02:36 Power Query Merge
05:47 The Power Query Merge Warning
07:27 Using a Data Model Relationship (Power Pivot)
10:12 Wrap Up

Connect with me

Did you know I've written a book "Power BI for the Excel Analyst"?

[JJ_TheGreat]

7:17 Interesting, I look forward to that video! 😀

[excelemployeeleavetracker]

Great video... this shows the benefits of all three methods within Excel.

[IvanCortinas_ES]

I love the old Power Pivot. I believe that the tool emerged at a time of transition and was never realistically taken into account by companies. Without a doubt, Power Query is a very powerful tool, present and future of our lives ;)

[JJ_TheGreat]

3:53 But how do you do that? Is it in the settings somewhere?

[JJ_TheGreat]

6:27 Yeah, you have to make sure you have a list of unique values before you do a merge queries with Power Query!

[GeertDelmulle]

Nice comparison, Wyn.
BTW: in your scenario it suffices to refresh the Power Pivot table, just like you would an output table.

[DiptaGhossan]

I learned a lot for your videos, and after learning for few days, i can already start implementing power Query and power pivot for my data. I have a question though, i have 7 tables that connects to each other, however why is it that only 3 tables shown in the field item list of my pivot? how do i get the rest also in? thanks in advance for your advice

[Adam_K_W]

Another good use case for Power Query is when you're merging tables from an external source that is subject to change (i.e. have more and more records added as time goes by). Simply refreshing everything will add the new rows to the table you've loaded to as you go. With XLOOKUP, if you aren't necessarily using Excel Tables, you'd have to manage your formulas as the data sets update.

[pascaljoly]

when i try to do a 2-way Xlookup but with tables on different sheets within the same file or from 2 different files, it doens't work. it only works if both tables are on the same sheet. is there a fix?

[ze]

You can add tables to data model without query them in power query. It avoids to duplicate the tables in pivot table panel.

[oldschoolreviewer]

How to address in power query one to many relationships?

[misspink]

I wish power query could do many to many relationships. how do you get around this?

[chiragdabhi]

I have large raw data...approx 19lakh raw...when I am going to use power pivot from power query that data..then only count value shown.i found error when conver to average or sum value.i already remove null value and data convert to whole number.
Any thing else need to do?
I am using Excel 2016 inbuilt power query.

[williamrhopkins]

Power Pivot doesn't handle many to many relationships well. Power Query does. Also PQ lets you use many different join types -- the anti join is good for finding differences between tables.

[roywilson]

Surely the sum of cost should show the total cost per item, ? It would have been fairly easy to multiply the units by the cost in either Power Query or Power Pivot.

[iduncanw]

Nice 👍, however....
I don't understand why the "Sum of Cost" in the Power Pivot isn't actually a Sum? If you did the XLOOKUP or Power Query merge and had a Pivot Table it would have 3 for Apple, 6 for Banana etc. Surely this could be a legitimate use case and the Power Pivot option just looks wrong and is not the same result.

It's like it hasn't really joined them as if I did this via a normal database join, I'd get a Sum.

[JJ_TheGreat]

I don't understand - I thought you were going to attempt the same process (to get the same result) through PowerPivot as the XLOOKUP and Power Query merge queries. Couldn't have you done that, in order to get the Item, Date, Units, Name and Cost together into 1 table, like you did before? You did not show how we could bring in the attributes from the other table in order to accomplish this task - as an alternate method.

[gerbherb]

In my opinion XLOOKUP and PowerQuery are the two ways to combine tables, whereas Powerpivots are summarising table data. Essentialy two different things.