Vlookup - ish with wildcards in PowerPivot

[Jakub]

Hey !

I think im struggling with the impossible here. What I need to do, is to manage a relationship with two databases in which I want to see if a particular value of DB1 exists anywhere in DB2

for example.


DB1
A
B
C
D
E

DB2
A,B,C
A,E
A



and then ideally i would have in DB1

A 1
B 1
C 1
D 0 (missed out, because it doesnt appear anywhere)
E 1


In excel I solved the prblem by index/match + ** wildcards. Is it feasible in powerpivot ?

Jakub

 

[Jakub]

edit: I found something like this on the internet - “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 - PowerPivotPro but it just cannot work ! I need to check if A,B,C etc. "exists somehwere" in the column on the right. Any ideas for powerivot ?

		CHECK
	A				A,B
	B				Z
	C				W
	D				Q
	E				C,D,F
	F				H
	G				I
	H				J

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[Matt Allington]

Power Pivot is a reporting database and is not really built to handle this type of thing. This is more like a traditional DB task (indeed a spreadsheet). Can you broaden the discussion about what you are actually trying to do? I would think that Power Query may be a useful tool for such a task.