Forum Discussion
Solved
Power Query M - get value from column other table based on condition
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!!
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"
1 Reply
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"