Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
typod
New Member

XLookup Replacement

Hi have a spreadsheet that is doing a lot of calculations in order to gather data from multile sheets where multiple fields needs to be matched.  I have got most of the calculations performing really well, but there is an XLookup command that is currently taking several minutes to process around 8,000 lines.

 

=XLOOKUP(A2 & B2 & C2,'Schedule'!A:A &'Schedule'!B:B & 'Schedule'!C:C,'Schedule'!AN:AN) - This is in column F for this example

 

What this does it match 3 elements in one sheet againsts the same 3 entries in another and then returns the value in AN of the second sheet to the session column in sheet 1.  An example of the data:

 

Sheet 1

   

ABCDEF
1Name    Region   Location   DeptSession
2BobEurope   London     IT 
3BertEuropeLondonIT 
4SallyUSNew YorkFinance 
5SimonUSSan FranciscoFinance 

            

Schedule

Region   Location   DeptSession
Europe   London     IT 1
EuropeLondonIT 1
USNew YorkFinance 2
USSan FranciscoFinance 3

 

Would Power Query be a better way of acheiving the same result and would it be faster.  Happy to look at any other suggestions to speed things up as I may be totally heading in the wrong direction.  

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @typod ,

 

Yes, I would generally say PQ is far easier/faster for this type of thing.

 

In PQ, all you would need to do is:

1) Import your Sheet1 and Schedule tables

2) Select your Sheet1 table query and go to Home tab > Merge Queries

3) Select you Schedule table from the dropdown at the bottom of the dialog that opens

4) Holding down Ctrl, select the three fields in your Sheet1 table that you want to match to the schedule table

5) Do the same again IN THE SAME ORDER on your Schedule table below (you should see little numbers in the column headers of both table previews showing the selections and the order in which they will be matched)

6) Keep the default Left Outer Merge type

7) Expand the resulting nested-table column, selecting your Schedule[Session] column to expand

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
typod
New Member

Thanks so much.  I will take a look at this now

BA_Pete
Super User
Super User

Hi @typod ,

 

Yes, I would generally say PQ is far easier/faster for this type of thing.

 

In PQ, all you would need to do is:

1) Import your Sheet1 and Schedule tables

2) Select your Sheet1 table query and go to Home tab > Merge Queries

3) Select you Schedule table from the dropdown at the bottom of the dialog that opens

4) Holding down Ctrl, select the three fields in your Sheet1 table that you want to match to the schedule table

5) Do the same again IN THE SAME ORDER on your Schedule table below (you should see little numbers in the column headers of both table previews showing the selections and the order in which they will be matched)

6) Keep the default Left Outer Merge type

7) Expand the resulting nested-table column, selecting your Schedule[Session] column to expand

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors