• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Index and Match Function Replacement|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Power Query Index and Match Function Replacement|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryPower Query Index and Match Functio…
sp_PrintTopic sp_TopicIcon
Power Query Index and Match Function Replacement
Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
1
December 1, 2021 - 2:00 am
sp_Permalink sp_Print

I have been using Power Query for a little over year doing basic cleanup and merging of data for reports. I am treading new territory with replacing index and match in Power Query, which I know are not function in PQ.

The below formula is used cell D1 thru D5. The same formula is used in cells E2:E5. If the list code and AST1 have a match on the Rep Table, then it will show the Rep Table Location as shown in cell D4. If there is no match, BLANK1 or BLANK2 will show as shown in cells D2:D3 and E2:E3.

 

=IFERROR(INDEX('REP TABLE'!$F$2:$F$3577,MATCH(1,(Formula!K2='REP TABLE'!$B$2:$B$3577)*(Formula!A2='REP TABLE'!$D$2:$D$3577),0)),"BLANK1")

 

The attached file, T-Sample, is a small example of what I am working with. The only data not included on this sheet is sales information. I have tried to create some type of helper column with concatenating the salesrep & list code on the Rep Table tab, but I run into an issue with one to many and my numbers get skewed.

 

Your help is much appreciated! If you need anything additional, please let me know.

Kind regards,

Alicia

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 1, 2021 - 5:23 pm
sp_Permalink sp_Print sp_EditHistory

Hi Alicia,

You have here a good tutorial describing exactly your case:

https://www.myonlinetraininghu.....ry-vlookup

Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
3
December 2, 2021 - 3:05 am
sp_Permalink sp_Print

Hi Catalin, 

Thank you for the reply. The link for the tutorial took me back to my original post. Can you please send me the tutorial link for my case?

Thank you! 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
December 2, 2021 - 3:12 am
sp_Permalink sp_Print

Sorry, fixed the link in the previous post.

The equivalent of Index-Match or VLOOKUP in Power Query is the Merge operation, it's much stronger and flexible than a formula, you can use more than 1 key columns to identify a record.

Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
5
December 2, 2021 - 3:34 am
sp_Permalink sp_Print

No problem. When I use the Merge operation on my file, I am running into issues of the sales showing the same for each row as well as the same reps. I was hoping there may have been another way to do this. 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
December 2, 2021 - 4:41 am
sp_Permalink sp_Print

Please share more details, an example of your wrong attempts will help.

In Merge settings, you can associate more columns as keys, in the attached example I used Rep and Code columns. First table should be sales, second should be Rep Table, Left Outer join type.

Is this what you are looking for?

Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
7
December 2, 2021 - 5:56 am
sp_Permalink sp_Print sp_EditHistory

I created a new sample file with more details and the issue I am experiencing when using the merge operation. When I merge the AST1 on the Sales Query and SalesRep on the Rep Query, remove the columns I do not need to only show the Rep Location, you'll see the duplicate sales for 20 and 21. 

 

I will also need to lookup the Rep Location for AST2 and AST3, but based on the merge operation, it will only duplicate the sales. See attached file. 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
December 2, 2021 - 2:41 pm
sp_Permalink sp_Print sp_EditHistory

That's what I mentioned above: you have to merge based on more column keys, as I did in the previous message sample file. I used Rep AND Code columns as keys that identifies a UNIQUE entry in Rep table. If you use only Rep, then it will find more than 1 match in the rep table, all matches will be returned.

Your merge formula:

= Table.NestedJoin(#"Changed Type1", {"AST.1"}, Rep, {"SalesRep"}, "Rep", JoinKind.LeftOuter)

Correct formula, with 2 columns as keys:

= Table.NestedJoin(#"Changed Type1", {"AST.1", "MFG"}, Rep, {"SalesRep", "MFG"}, "Rep", JoinKind.LeftOuter)

See image attached for a correct key mapping, a unique entry in Rep table is identified by 2 columns: AST.1 and MFG (LIST Code), not just Rep.

Also, please follow the tutorial provided, if you don't know how to map multiple columns for merging.

An excel Index-Match combination returns ONLY the first match found, but in PQ, if there are more than 1 matches, all matches will be returned, causing duplicate rows.

If you look at the merged step column before expanding the merge results, you will see that the table contains multiple rows. It is possible to keep only the first row, if you don't want to use multiple merge keys, but it's not right.

1.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 1.jpg (67 KB)
Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
9
December 3, 2021 - 1:32 am
sp_Permalink sp_Print

Thank you. Now that  the merge was done for AST.1, I need to do the same thing for AST.2 and AST.3. When I do the merge for AST.2, my results are null. Is there another way to use that function again to produce the same results showing the Rep Location?

 

Capture.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Capture.PNG (230 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
December 3, 2021 - 1:45 am
sp_Permalink sp_Print sp_EditHistory

You have to take a closer look at the source data, if the results are not as expected.

If you look at AST.2 column, the names in that column have a space or 2 before the name, it obviously needs some cleaning before merge.

Avatar
Alicia Bucci
Member
Members
Level 0
Forum Posts: 7
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
11
December 3, 2021 - 2:12 am
sp_Permalink sp_Print sp_EditHistory

Thank you so much for your help! I was able to get the space before the name cleaned up and everything populated like I needed it. 

 

In Excel I used the IF function to determine the actual location of the sale. My formula in Excel was:

=IF(M5=N5,M5,IF(AND(M5="BLANK1",N5="BLANK2"),"Investigate",IF(AND(M5<>"BLANK1",N5="BLANK2"),M5,IF(AND(M5<>"BLANK1",N5<>"BLANK2",M5<>N5),"300-Corporate",IF(AND(M5="BLANK1",N5<>"BLANK2"),N5,"ERROR")))))

 

If I add a conditional column in PQ, how can I rewrite the above formula to bring back the actual location of the sale?

If AST1, AST2 and AST3 are Blank, then I need to investigate.

If AST1 has a territory and AST2 and AST3 are blank, return the territory for AST1.

If AST1 is Blank and AST2 has a territory and AST3 is blank, return the territory for AST2.

If AST1, AST2, AST3 has different territories, return 300C.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
December 6, 2021 - 4:06 pm
sp_Permalink sp_Print sp_EditHistory

Hi Alicia,

You have to use column references instead of cell references:

ActualLocation= if [AST.1]=[AST.2] then [AST.1] else if ([AST.1] ="BLANK1" and [AST.2]="BLANK2") then "Investigate" else  ....

You have a tutorial here: https://www.myonlinetraininghub.com/power-query-if-statements

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti, Misael Gutierrez Sr.
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.