Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!!
Solved! Go to 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:
Proud to be a Super User!
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:
Proud to be a Super User!
@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())
User | Count |
---|---|
106 | |
90 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |