Power Query M - get value from column other table based on condition

[BenjaminSNN]

Hello everyone,

Hopefully you can help me with the following situation in Power Query.

Situation:
We have projects within programs. The project can be in different phases. For each phase in a programm a maximum term (days) is set. This maximum differs between programms and phases. 

Table 1

Program	phase1	phase2	phase3
A	7	35	14
B	21	35	21
C	25	42	7

Table 2

Project	Program	phase	started	ended
1	A	1	1-11-2022	7-11-2022
2	A	3	3-11-2022	18-11-2022
3	B	2	5-11-2022	24-11-2022
4	C	2	1-11-2022
5	C	1	10-11-2022

Desired outcome:

I want to know for each project the maximum term based on program and phase. Hopefully by referencing the correct column in the other table and not adding al those columns.

Project	Program	phase	started	ended	term
1	A	1	1-11-2022	7-11-2022	7
2	A	3	3-11-2022	18-11-2022	14
3	B	2	5-11-2022	24-11-2022	35
4	C	2	1-11-2022		42
5	C	1	10-11-2022		25

Another solution would be to make a combined column of 'program - phase' in both tables. Pivot table 2 and then join it on that column. But I am hoping for a solution with 1 step/formula

Thanks in advance!!

[ddpl · Accepted solution]

@BenjaminSNN ,

In Power Query...

Table 1...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIHYmNTIGFoohSrE63kBGQaGcIEgSyQoDOICeKbGIG1xMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Program = _t, phase1 = _t, phase2 = _t, phase3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"phase1", Int64.Type}, {"phase2", Int64.Type}, {"phase3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Program"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","phase","",Replacer.ReplaceText,{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Phase"}, {"Value", "Term"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Program", "Phase"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Program-Phase")
in
#"Merged Columns"

Table 2...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtYKhraKhrZARimkOZsTrRSkZQNcYgCWO4GkMLJDUgOScgBms2hasxMkFSYwIUcIapQdgFljOFyoHcYWiAJBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Program = _t, phase = _t, started = _t, ended = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Program", type text}, {"phase", Int64.Type}, {"started", type date}, {"ended", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Program-Phase", each Text.Combine({[Program], Text.From([phase], "en-US")}, "-"), type text),
#"Merged Queries" = Table.NestedJoin(#"Inserted Merged Column", {"Program-Phase"}, #"Table 1", {"Program-Phase"}, "Table 1", JoinKind.LeftOuter),
#"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Term"}, {"Term"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table 1",{"Program-Phase"})
in
#"Removed Columns"