Forum Discussion
Add column to Table A from Table B
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!!!
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:
2 Replies
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())
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:
