PQ- Find & replace values based on input table (another table)

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Hi There,
I am looking for a unique solution in Excel power query.

I have two tables
  1. Base Table – It has 14 columns and thousands of rows,
  2. Input Table – This is the input table for changes required in the Base table.
I want the power Query to match A column ( Pin Code) in the Base Table with A Column ( Pin Code) in the Input Table , if it matches the power query to import/replace values from the Input table to Base Table, however, I want to import/replace only below mentioned columns, if not matches Power Query to keep Original data as it is.

Columns to be replaced /Imported
APSC Name, Scenario, Model Category, Geography, GCS Claimer ID, ASC City, ASC Pincode, State, Branch, Distance between ASC to Upcountry Pincode

In another word, I don’t want to replace/import any values of columns - Pin code, MAG, #Type, District in base table.

I am Okay to use both the table as inputs and output in the 3rd table.

Please suggest a workaround

Input File sample
New Mapping Input File.xlsx
ABCDEFGHIJKLMNO
1Pin codeDistrictAPSC NameScenarioModel Category#TypeMAGGeographyClaimer IDASC CityASC PincodeStateBranchDistance between ASC Remarks If Any
2201304NoidaDigital India pvt ltd1 DayHome ServiceAppliancesLocalBZS035815Noida201301Utter PradeshDelhi10
3201306NoidaDigital India pvt ltd1 DayDealer ServiceAppliancesUp CountryBZS035815Noida201301Utter PradeshDelhi40
Input
Cells with Data Validation
CellAllowCriteria
D2:D3List1 Day,7 Days



Base Table sample

Pin codeAPSC NameScenarioModel CategoryMAGGeographyGCS Claimer ID#TypeDistrictASC CityASC PincodeStateBranchDistance between ASC to Upcountry Pincode
201304Caring Appliances Pvt Ltd1 DayHome ServiceD12LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceD13LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE18LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE25LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE30LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE35LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE38LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE52LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE84LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE85LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE92LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE95LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF11LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF12LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF15LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF73LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE34LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE37LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE91LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE86LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE13LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF72LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceE20LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF71LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201304Caring Appliances Pvt Ltd1 DayHome ServiceF74LocalBZS023145DAPCNoida201301UPDelhi & NCR0
201203Caring Appliances Pvt Ltd1 DayHome ServiceD13LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE30LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE86LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceF73LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE18LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE52LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceF11LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE39LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI27LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201305Caring Appliances Pvt Ltd1 DayHome ServiceE39LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceI27LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceD13LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE30LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE84LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF71LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE37LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE91LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF74LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE20LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF12LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE13LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE40LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE95LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE34LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE85LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF72LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE38LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE92LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceD12LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE25LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF15LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE35LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE86LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceF73LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE18LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201305Caring Appliances Pvt Ltd1 DayHome ServiceE52LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE35LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE86LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF73LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE18LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE52LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF11LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE39LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceI27LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceD13LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE30LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE84LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF71LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE37LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE91LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF74LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE20LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF12LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE13LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE40LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE95LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE34LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE85LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceF72LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE38LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceE92LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201306Caring Appliances Pvt Ltd1 DayHome ServiceD12LocalBZS023145DAPCNoidaNoida201301Utter PradeshDelhi0
201203Caring Appliances Pvt Ltd1 DayHome ServiceL09LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE15LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE42LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceF02LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE04LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceL83LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI36LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE01LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceL79LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE17LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE43LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE07LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE55LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE14LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE41LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI37LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE02LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI40LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE10LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI38LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceE53LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI75LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceF03LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI39LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
201203Caring Appliances Pvt Ltd1 DayHome ServiceI26LocalBZS023145DAPCSector -37Noida201301UPDelhi & NCR4.5
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You must log in or register to reply here.

Similar threads

M
  • Question
Replace null values with table header
Replies
0
Views
341
Martin sherk
M
CubaRJ
  • Question
Returning a Set of Values from 3 different tables based on 2 criterias
Replies
0
Views
374
CubaRJ
CubaRJ
GraH
  • Question
Creating a Power Query function that can replace values for any field
Replies
1
Views
955
GraH
GraH
S
  • Solved
Power Query: Merging Columns based on Article ID
Replies
3
Views
594
Silverborn
S
A
  • Solved
Power Query - filter & lookup values based on another table
Replies
7
Views
2K
Automatrix
A

Forum statistics

Threads
1,215,330
Messages
6,124,308
Members
449,152
Latest member
PressEscape

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top