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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You must log in or register to reply here.

Similar threads

CubaRJ
  • Solved
How to replace text in a column based on values of next and previous row?
Replies
7
Views
392
CubaRJ
CubaRJ
M
  • Question Question
Replace null values with table header
Replies
0
Views
414
Martin sherk
M
CubaRJ
  • Question Question
Returning a Set of Values from 3 different tables based on 2 criterias
Replies
0
Views
488
CubaRJ
CubaRJ
GraH
  • Question Question
Creating a Power Query function that can replace values for any field
Replies
1
Views
1K
GraH
GraH
S
  • Solved
Power Query: Merging Columns based on Article ID
Replies
3
Views
698
Silverborn
S

Forum statistics

Threads
1,223,950
Messages
6,175,584
Members
452,653
Latest member
craigje92

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