Compare values in 2 colums return value from 3rd column in a new colum

[tengels]

Hi all,

Trying to get my head around the following in Power Query.

I'm trying to compare 2 columns from 2 seperate table to find a common value

I have table Shipment_Data with column Consol ID and table Consol_Data with colum Reference.

The values in column Reference should be contained in the colum Consol ID, if it matches it should then copy the value from table Consol_Data, column MAWB from the same row and create a new column in table Shipment_Data with the respective value from table Consol_Data, column MAWB

As there might be multiple values in column Consol ID.

[v-yangliu-msft]

Hi  @tengels ,

I created some data:

Shipment_Data:

Consol_Data:

Here are the steps you can follow：

1. Copy Shipment_Data in Power Query to form a Copy Table.

2. Home – Check [Consol ID] – Split Column – By Delimiter.

3. Check [Consol ID.1] and [Consol ID.2] - Transform - Unpivot Column.

Result:

4. Create calculated column.

Flag =
MAXX(FILTER(ALL(Copy),
'Consol_Data'[Reference] = 'Copy'[Value]),[MAWB])

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[tengels]

Table1:

Master Bill	Consol ID
07457342331	C2202200835
00171664994	C2202374237
36984573355	C2202200792
07146058036	C2202377395
07146058036	C2202377395
07146058036	C2202377395
07457341723	C2202200744
61535930031	C2202200700
C2202364686	C2202200729, C2202364686
07146058036	C2202377395
71632345806	C2202158469
07146054260	C2202181411, C2202200735
61828220091	C2202158831
50112781812	C2202377481
07146051036	C2202200770, C2202417899
61828220091	C2202158831
61828220091	C2202158831
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
S0225112022VIE	C2201709488, C2202200835, C2202419076
07146054260	C2202181411, C2202200735

Table 2

Reference	Master Bill
C2202200722	07146051972
C2202200774	07146052020
C2202200837	07146052075
C2202161501	17652177974
C2202158467	61828220581
C2202161490	17652191716
C2202158728	17652191731
C2202158685	61828220356
C2202377473	50112781414
C2202377512	50112781451
C2202373800	87641125512
C2202377428	50112782814
C2202411697	11266136582
C2202200775	07146050885
C2202200770	07146051036
C2202298522	07146056205
C2202200765	07146053615
C2202200733	07146053431
C2202377481	50112781812
C2202377498	50112781392
C2202378307	50112781403
C2202377436	50112781440
C2202377647	50112782803
C2202158437	07756796294
C2202158452	15786602025
C2202158486	07756803165
C2202158476	07756803250
C2202158772	61828219951
C2202158831	61828220091
C2202158464	15787404634
C2202158779	15787422274
C2202158508	93315611470
C2202235784	07146053361
C2202418678	07146049872
C2202200735	07146054260
C2202158587	15786602176
C2202158823	15786602434
C2202158722	61827740484
C2202158783	61828219984
C2202158565	61828220393
C2202158742	61827740930
C2202158861	61828220286
C2202158469	71632345806
C2202158500	29784162982
C2202206384	61532398236
C2202389927	61535931350
C2202200811	61535929961
C2202411424	61535929681
C2202200814	61535930646
C2202200704	61535928384
C2202200813	61535931206
C2202200668	61535929935
C2202200839	61535928955
C2202200731	61535928513
C2202200742	61535928944
C2202200786	61535930016
C2202200683	61535931453
C2202200715	61535928970
C2202200700	61535930031
C2202200661	61535928852
C2202102742	01693617204
C2202200807	36984573145
C2202374331	001
C2202374237	00171664994
C2202374264	00171308241
C2202409792	36984556032
C2202200793	36984573171
C2202399763	36984555962
C2202200792	36984573355
C2202200729	36984573300
C2202200829	07457341443
C2202200835	07457342331
C2202377404	07146963792
C2202377394	07146058025
C2202377395	07146058036
C2202377611	07146057944
C2202200744	07457341723

End result for table 1:

Master Bill	Consol ID
07457342331	C2202200835
00171664994	C2202374237
36984573355	C2202200792
07146058036	C2202377395
07146058036	C2202377395
07146058036	C2202377395
07457341723	C2202200744
61535930031	C2202200700
36984573300	C2202200729
07146058036	C2202377395
71632345806	C2202158469
07146054260	C2202200735
61828220091	C2202158831
50112781812	C2202377481
07146051036	C2202200770
61828220091	C2202158831
61828220091	C2202158831
07457342331	C2202200835
07457342331	C2202200835
07457342331	C2202200835
07457342331	C2202200835
07457342331	C2202200835
07457342331	C2202200835
07457342331	C2202200835
07146054260	C2202200735

So in the end it should pick the corresponding value of Master Bill from Table 2 depending on which Consol ID value in Table 1 matches the Reference value in table 2