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
wyn.bio.link/
accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/

[@russ6010]

Best channel on KZbin

[@AccessAnalytic]

Hah thank you ☺️

[@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 ;)

[@AccessAnalytic]

Wise words Iván

[@excelemployeeleavetracker1274]

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

[@AccessAnalytic]

Thanks

[@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.

[@AccessAnalytic]

Yep, good call

[@GeertDelmulle]

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

[@AccessAnalytic]

Thanks Geert, absolutely!

[@JJ_TheGreat]

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

[@AccessAnalytic]

Coming in a couple of days

[@AccessAnalytic]

New video : kzbin.info/www/bejne/d3LKq3yOjMx0ick

[@garethwoodall577]

Great tips Wyn!

[@AccessAnalytic]

Cheers Gareth

[@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!

[@AccessAnalytic]

Totally

[@williamrhopkins]

Depends what you want. If you have a legit many to many relationship then PQ is providing the correct results

[@ze0000]

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

[@AccessAnalytic]

You can Jose, but I wouldn't recommend that approach as Power Query adds a lot of robustness and flexibility if things change in the future. The "Add to data model" option is what we used to have to use before Power Query "load to data model" was an option. I totally agree that showing the non loaded tables in the Power Pivot Fields Panel is horrible and something the Excel team should disable. I (and other MVPs) have flagged this numerous times to them.

[@chandanlimma3443]

This is brilliant as ever. Thanks

[@AccessAnalytic]

Thank you Chandan

[@Luciano_mp]

Great tips! Thanks!

[@AccessAnalytic]

You’re welcome Luciano

[@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.

[@AccessAnalytic]

Yep, Many to Many is not allowed in Power Pivot. I’m a fan of the anti join 😀

[@kudifilipe9875]

Thanks, sharing

[@AccessAnalytic]

No worries

[@danjarupath]

Hello this is awesome 😎😎😎

[@AccessAnalytic]

Thanks

[@TheJoshtheboss]

Amazing channel.

[@AccessAnalytic]

Glad you enjoy it! Thanks

[@roywilson9580]

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.

[@AccessAnalytic]

Hi Roy, yep, wasn’t the aim of the video but a SUMX function would allow me to calculate units x cost on a row by row basis to give total cost

[@AccessAnalytic]

Yours and another comment has prompted next week's video 😁

[@roywilson9580]

@@AccessAnalytic I will look forward to seeing it :)

[@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

[@AccessAnalytic]

Check the top of the Pivot Table field list and you will see Active and All. Click the All option

[@madacraizdesigns4934]

GENIUS

[@AccessAnalytic]

Thanks

[@pascaljoly5752]

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?

[@AccessAnalytic]

Not sure what you mean by 2 way XLOOKUP. The sheets the tables are on shouldn’t matter for XLOOKUP

[@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.

[@AccessAnalytic]

Hi, I wasn't really thinking about demonstrating Total Cost in this video but I would need to write the following DAX formula to get the total =SUMX( tblData, tblData[Units] * RELATED(tblLookup[Cost] ) ) This function creates a temporary column of Units x Price and then SUMs it.

[@iduncanw]

@@AccessAnalytic thanks for the extra tip. Still seems strange behaviour that you need to do that.

[@AccessAnalytic]

@@iduncanw - you've prompted next week's video 😁

[@AccessAnalytic]

@@iduncanw new video: kzbin.info/www/bejne/d3LKq3yOjMx0ick

[@oldschoolreviewer1133]

How to address in power query one to many relationships?

[@AccessAnalytic]

Can you explain a bit more? A power query merge will bring in all matches from the many side

[@oldschoolreviewer1133]

already have.. watch it from some of your Videos.. Thanks a lot..

[@gerbherb8215]

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

[@AccessAnalytic]

The end result is the key here. If you want to report sales by region name and all you have is region ID, but in another table is ID and region name , then all 3 approaches are valid options depending on your reporting need.

[@gerbherb8215]

@@AccessAnalytic You are correct but I think that's not where my focus was, so let me try to put it in another way: with XLOOKUP and PowerQuery you are combining tables, with pivots you are combining AND summarising them. In one of the comments here I saw confusement about the fact that your pivot had different numbers. Maybe good to explain this summarising aspect of pivots.

[@AccessAnalytic]

Fair point, thanks

[@1976misspink]

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

[@AccessAnalytic]

Power Query will create a combined output of the 2 tables with the double ups that appear as per my video. PowerPivot can’t create many to many. Normally creating a bridging table is the best approach.

[@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.

[@AccessAnalytic]

At 9:49 I’ve created a pivot table containing elements from both tables. This is the beauty of the Power Pivot approach is that you only have to display what you need

[@chiragdabhi4929]

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.

[@AccessAnalytic]

If the column you are averaging or summing is set to a whole number data type then it should work. Are you writing a DAX measure for your calculation?

[@chiragdabhi4929]

@@AccessAnalytic no dax.simple raw data...that convert into whole number(kpi value) ...in raw data some null and Nan value there so just filter out in power query.after power query going for power pivot.i need district wise kpi so district wise count shows but that data not convert into some our average value.

[@AccessAnalytic]

Most likely reason is other non-number value in your column. Also I strongly recommend you use DAX for all displayed values and aggregations to future proof your report

[@chiragdabhi4929]

@@AccessAnalytic thank you for valuable reply. I have learn lots of from your channel.🙏

[@roywilson9580]

I have experienced a similar problem with Excel 2016, it only seems to understand certain data-types - in my case it refused to accept a currency type as a number and would not accept it even after changing the type to number in Excel I have even had problems with decimal number and integer type not being recognised. I have also had problems in 2016 with excel not understanding date data types. I think it may just be a problem with Excel 2016 as I haven't run into this problem with any of the later versions of Excel. You Will also find that Excel 2016 sometimes has problems with data imported from Sage etc. All very annoying when you are working with extremely large datasets that require manipulation before loading to Excel.

[@JJ_TheGreat]

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

[@excelemployeeleavetracker1274]

Go to Data on the top ribbon...then follow his instructions

[@AccessAnalytic]

Get Data-> Query Options -> Data load-> Specify custom default load settings & uncheck to load to boxes