Power Query to update other files based on input

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Greetings!!!


I am working on a project where we need to reallocate agents frequently,

I am looking for a solution in Power Query where we can upload Input file PQ will check/match and merge details and update Mather as well as Log file.


So we have 3 sets of file
  1. Input- Pincode Mapping Where we get change requests form filed team
  2. Mather – Based on the input file, we need to update the matcher file so that manager can allocate jobs accordingly
  3. Log File – where we need to record all changes made
Logic:-

PQ needs to look in Pincode ( Column A) & Del Type ( Column E) together in the Input file and match the details in Pincode ( Column A) & Del Type ( Column D) in the matcher file.

If details are matching PQ to copy all the details from the Input file and paste in matcher file if Not matching PQ to keep original details ( Which are available in Matcher file) as it is – No changes required.

Note- Matcher has 2 sheets ( Sheet 1 & Sheet 2) PQ to check the details in both the sheets and update details wherever Pin Code and Del Type are matching.

Once this is done PQ to Append Log File, here PQ will paste data which is transferred ( Copy Pasted) from the Input file to Matcher ( Means where Pin code and Del Matching and copied from Input to Matcher file) and automatically put activity date (Current system date ) in a log file.

I know this is a bit critical job, please let me know if this is possible in PQ or suggest any other system/app which will help me perform this task.

Attaching Sample data for ready reference.

Input- Pincode Mapping.xlsb
ABCDEFGHIJKLMNO
1Pin codeDistrictAgent NameScenarioDel Type#TypeTran TypeGeographyAgent IDASC CityAgent PincodeStateBranchDistance between Agent to Upcountry PincodeRemarks If Any
2221301SANT RAVIDAS NAGARSantosh NikamSame DayDoor StepDeliveryBikeUp country2552890VARANASI221010UPUP & UK20
3221310SANT RAVIDAS NAGARKiran KumarNext DayOn FloorDeliveryLocal TransUp country2552890VARANASI221010UPUP & UK34
4221406SANT RAVIDAS NAGARAmit AnandNext DayOn FloorDeliveryLocal TransUp country2552890VARANASI221010UPUP & UK40
5231001MIRZAPURSatish MoreSame DayDoor StepDeliveryBikeLocal2552890VARANASI221010UPUP & UK17
Matcher Update
Cells with Data Validation
CellAllowCriteria
H2:H5ListLocal,Upcountry


Matcher.xlsx
ABCDEFGHIJKLMNO
1Pin codeAgent NameScenarioDel TypeMAGGeographyAgent ID#TypeDistrictASC CityAgent PincodeStateBranchDistance between ASC to Upcountry PincodeRemarks
2221301Surendra ManeSame DayDoor StepD12LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
3221301Surendra ManeSame DayDoor StepD13LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
4221301Surendra ManeSame DayDoor StepE01LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
5221301Surendra ManeSame DayDoor StepE02LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
6221301Surendra ManeSame DayDoor StepE04LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
7221301Surendra ManeSame DayDoor StepE07LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
8221301Surendra ManeSame DayDoor StepE10LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
9221301Prasad AokSame DayDoor StepE13LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI9.2
10221301Surendra ManeSame DayDoor StepE14LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI9.2
11221301Surendra ManeSame DayDoor StepE15LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
12221301Surendra ManeSame DayDoor StepE17LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI9.2
13221380Girish OakSame DayDoor StepE18LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
14221380Girish OakSame DayDoor StepE20LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
15221380Girish OakSame DayDoor StepE25LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
16221380Girish OakSame DayDoor StepE30LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
17221380Girish OakSame DayDoor StepE34LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
18221380Sanjay JadhavSame DayDoor StepE35LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI25
19221380Sanjay JadhavSame DayDoor StepE37LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI25
20221380Girish OakSame DayDoor StepE38LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
21221380Girish OakSame DayDoor StepE39LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI25
22221406Bhaskar KurtadkarSame DayDoor StepE40LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
23221406Bhaskar KurtadkarSame DayDoor StepE41LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
24221406Bhaskar KurtadkarSame DayDoor StepE42LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
25221406Bhaskar KurtadkarSame DayDoor StepE43LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
26221406Nana PatekarSame DayDoor StepE52LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI13
27221406Nana PatekarSame DayDoor StepE53LocalGCS045383Pick UpCentral DelhiWest Delhi110015DLDELHI13
28221406Bhaskar KurtadkarSame DayDoor StepE55LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
29221406Bhaskar KurtadkarSame DayDoor StepE84LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
30221406Bhaskar KurtadkarSame DayDoor StepE85LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
31221406Bhaskar KurtadkarSame DayDoor StepE86LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
32221406Bhaskar KurtadkarSame DayDoor StepE91LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
33221406Bhaskar KurtadkarSame DayDoor StepE92LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI13
34221301S J JoshiSame DayDoor StepE93LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI27
35221301S J JoshiSame DayDoor StepE95LocalGCS045383DeliveryCentral DelhiWest Delhi110015DLDELHI27
Sheet1


Log File.xlsx
ABCDEFGHIJKLMNO
1Pin codeDistrictAgent NameScenarioDel Type#TypeAgent IDASC CityAgent PincodeStateBranchNew BranchDistance between Agent to Upcountry PincodeGeographyLog Change Date
2221301SANT RAVIDAS NAGARSharad PawarSame DayDoor StepPickup2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand20Up country01-07-21
3221303SANT RAVIDAS NAGARRahul GandhiNext DayOn FloorPickup2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand23Up country22-06-21
4221304SANT RAVIDAS NAGARNana PatoleNext DayOn FloorDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand33Up country28-06-21
5221306VARANASIBhosleSame DayDoor StepDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand16Up country20-06-21
6221308SANT RAVIDAS NAGARAjit PawarSame DayDoor StepDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand39Up country03-06-21
7221309SANT RAVIDAS NAGARRahul GandhiNext DayOn FloorDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand38Up country29-06-21
8221310SANT RAVIDAS NAGARBhosleNext DayOn FloorDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand34Up country11-06-21
9221314SANT RAVIDAS NAGARNana PatoleSame DayDoor StepDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand28Up country14-06-21
10221401VARANASINimkarSame DayDoor StepPickup2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand30Up country21-06-21
11221402SANT RAVIDAS NAGARSharad PawarNext DayOn FloorPickup2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand40Up country11-06-21
12221404SANT RAVIDAS NAGARChkakankarNext DayOn FloorDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand45Up country02-06-21
13221406SANT RAVIDAS NAGARNana PatoleSame DayDoor StepDelivery2552890Mirzapur231001UPLucknowUttar Pradesh & Uttarakhand40Up country10-06-21
Log File




Thanks & Regards,

Sanket
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You must log in or register to reply here.

Similar threads

M
  • Solved
Power Query not refreshing the source files in folder
Replies
5
Views
593
smozgur
smozgur
S
  • Question
PQ- Find & replace values based on input table (another table)
Replies
0
Views
303
sanket_sk
S
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
T
  • Question
MS Access form to capture inputs and or update the database accordingly.
Replies
0
Views
757
TheMohitDembla
T

Forum statistics

Threads
1,214,601
Messages
6,120,465
Members
448,965
Latest member
grijken

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