XLOOKUP v Power Query v Power Pivot in Excel 

Hah thank you ☺️

Thanks

Thank you Chandan

Wise words Iván

Cheers Gareth

You’re welcome Luciano

Glad you enjoy it! Thanks

Thanks Geert, absolutely!

No worries

Thanks

Yep, good call

Thanks

Coming in a couple of days

New video : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AAgzIXWgASg.html

Totally

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

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

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.

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

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

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.

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?

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

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

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

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.

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

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

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

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

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

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

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

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.

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

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

@@iduncanw new video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AAgzIXWgASg.html

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

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.

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

Fair point, thanks