New calculated column using column from another table

Hi Anonymous ,

You need to merge Table2 into Table1 so it looks like this:

Then add a new custom column with the below code:

if (List.Contains({"a".."e"},[Type]) or [Injury] = "A") and [Transported Boolean] = "Transported" then true else false

 Note that Power Query is case sensitive, so even though in your example above you said "secondary['Type'] = A, or C, or D, or E" your secondary table had a, b, c, d, e, f. They aren't the same to PQ. You can change everything to upper/lowercase with a transformation before you do the merge. Just be aware, a=a, but A<>a.

Then when done, keep all but the temproary Type column. End result:

Total code for the first table, and it assumes your Table2 is exactly as you have it above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pCixLzigvyiktQUpVidaCUjoJgTEPvllyigyxlD5dDFTaBmYdNjisMeM6CYMzY9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Injury = _t, #"Transported Boolean" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Type"}, {"Type"}),
    #"Added Claimable MVA" = Table.AddColumn(#"Expanded Table2", "Claimable MVA", each if (List.Contains({"a".."e"},[Type]) or [Injury] = "A") and [Transported Boolean] = "Transported" then true else false),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Claimable MVA",{"ID", "Injury", "Transported Boolean", "Claimable MVA"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Claimable MVA", type logical}})
in
    #"Changed Type"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.