Add column to Table A from Table B

[Harvey85]

Hello,

I have the following tables:

1) Products:

ID	Zone	Desc	Value
11	1	Spark	5
12	1	Green	7
13	1	Blue	8
14	2	Yellow	10
15	2	Red	5

2) Categories:

ID	Category	End_Date
11	Seed Corn	1/2/2020
11	Seed Pumpkin	1/3/2020
11	Laboratory	1/4/2020
12	Laboratory	1/2/2020
13	Seed Pumpkin	1/3/2020

What I need to do is to add a new column in Table Products with the related End_Date (from Table Categories) from those Categories where field Category starts with "Seed" (in the other values like Laboratory the dates are not needed).

This would be the expected output:

ID	Zone	Desc	Value	End_Date
11	1	Spark	5	1/2/2020
11	1	Spark	5	1/3/2020
12	1	Green	7
13	1	Blue	8	1/3/2020
14	2	Yellow	10
15	2	Red	5

Do you know how could I do this?

Thank you!!!

[_AAndrade · Accepted solution]

Hi,

If you want to do it in Power Query use this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEC4eCCxKJsIG2qFKsDFDaCCrsXpabmAWlziLAxVNgppzQVSFlARE2ATJCGyNScnPxykBIDiIQpVCIoNQVidCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Zone = _t, Desc = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Zone", Int64.Type}, {"Desc", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, T_Categories, {"ID"}, "T_Categories", JoinKind.LeftOuter),
    #"Expanded T_Categories" = Table.ExpandTableColumn(#"Merged Queries", "T_Categories", {"Category", "End_Date"}, {"Category", "End_Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded T_Categories", "End_DateF", each if [Category] <> null and Text.Contains([Category], "Seed") then [End_Date] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "End_Date"})
in
    #"Removed Columns"

You don't need the first 2 steps because you alredy have your product table.
The final result shoul be this:

[amitchandak]

@Harvey85 , Create new columns in Table 2

Value= Maxx(filter(Table1, Table2[ID] = Table1[ID]  ), Table[Value])

Date New = if( containsstring(table1[Category], "Seed"), [Date], blank())