Log In
Register
Home
February 18, 2022
Offline
Dear all,
Hello i'm using microsoft office 365 and windows and I have some problem.
1. I have 2 file that need to combine using power query
a. 'File Weekly' is a key information from customer that update weekly.
b. 'WIP' is our internal file that contain status output for each section.
c. 'File Result' is the combined result file I want to use
how to combine it ? I've tried several ways and still having problems.
2. since the File Weekly is update with different file and different PO number, how can just refresh the data without doing combine it each week ?
3. PO number from WIP is a export file from a system, and somehow the 0 number on front each PO number is gone.
so there is a rule for adding the 0 number (each PO must contain 10 digit number or combination with letter)
a. if there is alphabet (e.g A,B or C), no need using 0
b. if the 1st number is 6, it must add 00 before 6.
c. if the 1st number is 1,2 or 3, it must add 0 before the 1st number
how to change the PO Number on WIP while doing combine using query ?
4. it's okay to add extra column after finish combine using power query like notes or remark ?
below is a link for example file
https://drive.google.com/drive.....sp=sharing
Kind Regards,
Justin R
See Answer
Moderators
January 31, 2022
Offline
Google drive wouldn't let me download the file so I replicated it by copying and pasting. You are aware that you can upload an Excel file to the forum directly (Attachments, Add Files, Start Upload)
Noticed that the Results sheet was not in line with the information in the WIP sheet. Changed to WIP sheet as I guessed you wanted to show different Status for some of the PO numbers.
The leading zeroes in the WIP table can be added using Text.PadStart([PO Number],10,"0"). I've done that in the attached file.
February 18, 2022
Offline
Hi Riny,
I'm sorry about the difficulty in downloading the file.
for the point number 3 it's solved.
for number one, i do the step like on picture 1 - 5. but somehow on pic no 5, i can't click ok. did i made a wrong step ?
if i used Append Queries it will happen like picture no 6.
Kind Regards,
Justin R
Moderators
January 31, 2022
Offline
Not sure what you are trying to achieve but to focus just on your question re Pic 5, when you do a merge you need to identify the key columns in each of the tables. In this case the key for merging is the PO Number, so select those columns and you can press OK.
February 18, 2022
Offline
Hi Riny,
Okay it's worked and have another issue.
there is some case that makes a PO is divided into 2 and then add the character behind the PO.
how to combine 2 files using power query and get the quantity from each section ('WIP' sheet, C until K column) based on the smallest number of each section ?
Kind Regards,
Justin R
Moderators
January 31, 2022
Offline
It's a bit of coding and works for this small example but I can't guarantee it will work on your real data set. Have a look at the attached file and see what happens.
Answers Post
1 Guest(s)
