Matching Columns in 2 Tables

[gauravnarchal]

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

1. From Table 2 identify the first character from coloumn “Identifier” of each row.
2. Now match the code column and first character of “Identifier” column in table 2 with code and category of table 1.

Return / Result

1. 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

[amitchandak · Accepted solution]

@gauravnarchal ,

new column in table 2= maxx(filter( Table1, Table2[Code] = Table1[Code] && Table1[Category] = left(Table2[Identifier],1)),Table1[Shipping])

[gauravnarchal]

Thanks @amitchandak 

[FrankAT]

Hi @gauravnarchal 

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)

[Anonymous]

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 CodeCom2

Then you can directly pull the column in the visual or you can use related function in the 2 nd table.