Forum Discussion
Matching Columns in 2 Tables
Dear All
I need your help.
I have two tables as below. In table 1 I have the data and in table 2 staff updates all the information.
Requirement
- From Table 2 identify the first character from coloumn “Identifier” of each row.
- Now match the code column and first character of “Identifier” column in table 2 with code and category of table 1.
Return / Result
- Shipping column of Table 1.
Can you please suggest if I can do this through Measure and by adding the Column?
Thanks
Gaurav
Table 1
| Code | Category | Shipping |
| SK | Q | Standard |
| WF | H | Standard |
| WF | A | Express |
| WF | U | Express |
| AW | B | Express |
| AW | K | Express |
| AW | B | Express |
| AW | L | Urgent |
| AW | F | Urgent |
| AW | L | Urgent |
| F3 | Y | Urgent |
| F3 | G | Urgent |
| AH | Y | Standard |
| AH | Y | Standard |
| UK | D | Express |
| UK | H | Express |
| GQ | W | Urgent |
| GQ | O | Urgent |
| GQ | D | Standard |
Table 2
| Code | Category | Identifier |
| SK | Q | QDKRTM |
| WF | H | HNOOWM |
| WF | A | UNOOWM |
| WF | U | UNOOWM |
| AW | B | BRTD |
| AW | K | BRTD |
| AW | B | BRTD |
| AW | L | BRTD |
| AW | F | LRTD |
| AW | L | LRTD |
| F3 | Y | Y |
| F3 | G | MRT |
| AH | Y | Y |
| AH | Y | Y |
| UK | D | HL1PYS/YS |
| UK | H | HL1PYS/YS |
| GQ | W | DHSMRTOW |
| GQ | O | DHSMRTOW |
| GQ | D | DHSMRTOW |
new column in table 2= maxx(filter( Table1, Table2[Code] = Table1[Code] && Table1[Category] = left(Table2[Identifier],1)),Table1[Shipping])
4 Replies
Step 1create a calculate column
Dax
CodeCom=[Code] &"" &[Category]
Step2. create another calculated column
Dax
CodeCom2=[Code] &""& LEFT([IENTIFIER,1)
and make a relationship between these tables using above new column CodeCom and CodeCom2Then you can directly pull the column in the visual or you can use related function in the 2 nd table.take a look at the following M-code:
// Table2 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDdCoMgGAbge/E4GGNX0JJyWHNLRUQ6GGSjkwXRye5+39cgZ+7gBd/HH1TnSDH1nmSkeCz+Oc1vGF56/1rGYfQz6TJHJAe7YyhvVbOaKaEzzFUI82M5RO9Mx5Yb6GdMq2gQnki6pk4Ez6+TNZuUJ2h2zVYrSNOq7w4Wze+qxjtRfGV9vFl5sDIwS7jCP8IbUCbhfGGCir9KI+0+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", {{"Identifier", each Text.Start(_, 1), type text}}) in #"Extracted First Characters" // Table1 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvZW0lEKBOLgksS8lMSiFKVYnWilcDegiAdWUUcgdq0oKEotLkYIhqIJOoYDBZywCXoTrdIHZG5RempeCULMDYsYmjo3YyA/EouYO5peD6g6FC9iFw0FudoFzZFgQQ80QXdQUIaj2gQW88ci5oJiTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Category = _t, Shipping = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Category", type text}, {"Shipping", type text}}) in #"Changed Type" // Merge1 let Source = Table.NestedJoin(Table2, {"Code", "Identifier"}, Table1, {"Code", "Category"}, "Table1", JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Shipping"}, {"Shipping"}) in #"Expanded Table1"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)new column in table 2= maxx(filter( Table1, Table2[Code] = Table1[Code] && Table1[Category] = left(Table2[Identifier],1)),Table1[Shipping])
Thanks amitchandak
