Can I use a Column as a source in Power Query

[ecrodrig]

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 Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	PM Comment
In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
In Progress				Peter	Project 1	9/20/2022: Finished with first group of tasks
In Progress				Peter	Project 2	9/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 Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	PM Comment
In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
In Progress				Peter	Project 1
In Progress				Peter	Project 2
On-Hold				Peter	Steady State
On-Hold				Peter	Steady State
Withdrawn				Peter	Steady State
Closed				Peter	Project 3
Closed				Peter	Project 4
Closed				Peter	Project 5
Closed				Peter	Project 6

Any help would be appreciated. Thanks.

 

[alansidman]

Book1
	A	B	C	D	E	F	G
1	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)
2	In Progress				Peter	Steady State
3	In Progress				Peter	Project 1
4	In Progress				Peter	Project 2
5	On-Hold				Peter	Steady State
6	On-Hold				Peter	Steady State
7	Withdrawn				Peter	Steady State
8	Closed				Peter	Project 3
9	Closed				Peter	Project 4
10	Closed				Peter	Project 5
11	Closed				Peter	Project 6
12
13
14	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	PM Comment
15	In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
16	In Progress				Peter	Project 1	9/20/2022: Finished with first group of tasks
17	In Progress				Peter	Project 2	9/20/2022: Delayed due to procurement
18
19
20	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	Table2.PM Comment
21	In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
22	In Progress				Peter	Project 1	9/20/2022: Finished with first group of tasks
23	In Progress				Peter	Project 2	9/20/2022: Delayed due to procurement
24	On-Hold				Peter	Steady State
25	On-Hold				Peter	Steady State
26	Withdrawn				Peter	Steady State
27	Closed				Peter	Project 3
28	Closed				Peter	Project 4
29	Closed				Peter	Project 5
30	Closed				Peter	Project 6
Sheet1

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"

 

[ecrodrig]

Book1
	A	B	C	D	E	F	G
1	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)
2	In Progress				Peter	Steady State
3	In Progress				Peter	Project 1
4	In Progress				Peter	Project 2
5	On-Hold				Peter	Steady State
6	On-Hold				Peter	Steady State
7	Withdrawn				Peter	Steady State
8	Closed				Peter	Project 3
9	Closed				Peter	Project 4
10	Closed				Peter	Project 5
11	Closed				Peter	Project 6
12
13
14	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	PM Comment
15	In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
16	In Progress				Peter	Project 1	9/20/2022: Finished with first group of tasks
17	In Progress				Peter	Project 2	9/20/2022: Delayed due to procurement
18
19
20	Project Current Step	Column3	Column4	Column5	PM Name	Project ID (RFS, SR, HVSR #)	Table2.PM Comment
21	In Progress				Peter	Steady State	9/20/2022: Transferred file to Box
22	In Progress				Peter	Project 1	9/20/2022: Finished with first group of tasks
23	In Progress				Peter	Project 2	9/20/2022: Delayed due to procurement
24	On-Hold				Peter	Steady State
25	On-Hold				Peter	Steady State
26	Withdrawn				Peter	Steady State
27	Closed				Peter	Project 3
28	Closed				Peter	Project 4
29	Closed				Peter	Project 5
30	Closed				Peter	Project 6
Sheet1

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!!!

 

[Peter_SSs]

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.