Can I use a Column as a source in Power Query

ecrodrig

Board Regular
Joined
Jan 21, 2022
Messages
99
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
So I have an excel sheet that has one column that needs to be updated with the current status. I want to be able to grab the information on that column and update the corresponding column on my master file using power query.

Here is the excel where the information will be updated. PM Comment is the Column that I need the input from.



Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)PM Comment
In ProgressPeterSteady State9/20/2022: Transferred file to Box
In ProgressPeterProject 19/20/2022: Finished with first group of tasks
In ProgressPeterProject 29/20/2022: Delayed due to procurement



Here is the master file where the input needs to go. I have added the first status to show how it should look. Note that in this File only Peter's name is showing but I will need to collect this information from 7 other people. The same column will be updated just a different person providing the status.

Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)PM Comment
In ProgressPeterSteady State9/20/2022: Transferred file to Box
In ProgressPeterProject 1
In ProgressPeterProject 2
On-HoldPeterSteady State
On-HoldPeterSteady State
WithdrawnPeterSteady State
ClosedPeterProject 3
ClosedPeterProject 4
ClosedPeterProject 5
ClosedPeterProject 6


Any help would be appreciated. Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Book1
ABCDEFG
1Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)
2In ProgressPeterSteady State
3In ProgressPeterProject 1
4In ProgressPeterProject 2
5On-HoldPeterSteady State
6On-HoldPeterSteady State
7WithdrawnPeterSteady State
8ClosedPeterProject 3
9ClosedPeterProject 4
10ClosedPeterProject 5
11ClosedPeterProject 6
12
13
14Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)PM Comment
15In ProgressPeterSteady State9/20/2022: Transferred file to Box
16In ProgressPeterProject 19/20/2022: Finished with first group of tasks
17In ProgressPeterProject 29/20/2022: Delayed due to procurement
18
19
20Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)Table2.PM Comment
21In ProgressPeterSteady State9/20/2022: Transferred file to Box
22In ProgressPeterProject 19/20/2022: Finished with first group of tasks
23In ProgressPeterProject 29/20/2022: Delayed due to procurement
24On-HoldPeterSteady State
25On-HoldPeterSteady State
26WithdrawnPeterSteady State
27ClosedPeterProject 3
28ClosedPeterProject 4
29ClosedPeterProject 5
30ClosedPeterProject 6
Sheet1


Power Query:
let
    Source = Table.NestedJoin(Table1, {"Project Current Step", "PM Name", "Project ID (RFS, SR, HVSR #)"}, Table2, {"Project Current Step", "PM Name", "Project ID (RFS, SR, HVSR #)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"PM Comment"}, {"Table2.PM Comment"})
in
    #"Expanded Table2"
 
Upvote 0
Solution
Book1
ABCDEFG
1Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)
2In ProgressPeterSteady State
3In ProgressPeterProject 1
4In ProgressPeterProject 2
5On-HoldPeterSteady State
6On-HoldPeterSteady State
7WithdrawnPeterSteady State
8ClosedPeterProject 3
9ClosedPeterProject 4
10ClosedPeterProject 5
11ClosedPeterProject 6
12
13
14Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)PM Comment
15In ProgressPeterSteady State9/20/2022: Transferred file to Box
16In ProgressPeterProject 19/20/2022: Finished with first group of tasks
17In ProgressPeterProject 29/20/2022: Delayed due to procurement
18
19
20Project Current StepColumn3Column4Column5PM NameProject ID (RFS, SR, HVSR #)Table2.PM Comment
21In ProgressPeterSteady State9/20/2022: Transferred file to Box
22In ProgressPeterProject 19/20/2022: Finished with first group of tasks
23In ProgressPeterProject 29/20/2022: Delayed due to procurement
24On-HoldPeterSteady State
25On-HoldPeterSteady State
26WithdrawnPeterSteady State
27ClosedPeterProject 3
28ClosedPeterProject 4
29ClosedPeterProject 5
30ClosedPeterProject 6
Sheet1


Power Query:
let
    Source = Table.NestedJoin(Table1, {"Project Current Step", "PM Name", "Project ID (RFS, SR, HVSR #)"}, Table2, {"Project Current Step", "PM Name", "Project ID (RFS, SR, HVSR #)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"PM Comment"}, {"Table2.PM Comment"})
in
    #"Expanded Table2"
THANKS!!!
 
Upvote 0
THANKS!!!
The marked solution has been changed accordingly.

@ecrodrig : In your future questions, please mark the post as the solution that answered your question instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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