How to do conditional formatting using a column in another table

[ecrodrig]

I want to do the following in Power Query (2 different Tables)

If Table 1 Column 1 = Table 2 Column 1 then "yes" else "No"

So basically if the cell in table 1 column 1 has the same information as the cell in Table 2 column 1 then I want it to say yes otherwise no.

I tried doing the following but everything comes out as no when I know it isn't the case:

=Table.AddColumn(#"Replaced Value2", "Custom", each if Table1 [Column 1] = [Column 1] then "it's there" else "not there"


Any help would be greatly appreciated. Thanks.

 

[JGordon11]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="FirstTable"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="SecondTable"]}[Content],
    addIndex = Table.AddIndexColumn(tbl1,"Index"),
    Result = Table.AddColumn(addIndex, "T1 T2 Compare", each if [T1Col1] = tbl2[T2Col1]{[Index]} then "it's there" else "not there")
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	T1Col1	T1Col2	T1Col3	T1Col4	T1Col5		T2Col1	T2Col2	T2Col3	T2Col4	T2Col5		T1Col1	T1Col2	T1Col3	T1Col4	T1Col5	Index	T1 T2 Compare
2	9	Z	O	A	F		9	31	18	44	12		9	Z	O	A	F	0	it's there
3	7	G	P	M	D		3	17	36	5	24		7	G	P	M	D	1	not there
4	5	H	R	W	D		5	4	7	3	14		5	H	R	W	D	2	it's there
5	6	E	U	I	G		8	16	42	27	6		6	E	U	I	G	3	not there
6	8	E	X	V	S		7	25	33	41	32		8	E	X	V	S	4	not there
7	2	I	J	F	N		2	19	20	30	43		2	I	J	F	N	5	it's there
8	1	K	J	T	Q		6	38	1	28	8		1	K	J	T	Q	6	not there
9	4	C	L	B	Y		4	2	9	21	22		4	C	L	B	Y	7	it's there
10	3	H	A	C	B		1	40	34	39	26		3	H	A	C	B	8	not there
11
Sheet5

 

[ecrodrig]

Hi,

Anyway to create this in a custom column? I already have a bunch of statements with in the let and wanted to just be able to create a custom column with the formula. Thanks

 

[ecrodrig]

Thanks for the info above, I attempted to do this in a custom column and when I create the index I get varing results. Table 2 only has 6 rows and Table 1 has 16 so when I create the index and put 0,0 as I get one "its there" If I do 1, 0 I get a different "Its there" and then if I do 2, 0 yet again a different its there I should see all three its there at the same time.

Hi,

Anyway to create this in a custom column? I already have a bunch of statements with in the let and wanted to just be able to create a custom column with the formula. Thanks