Wrong releationship of Table values

[EmPi]

Hi everybody,

I am facing following issue: I have two tables (see below Table A and Table B) and I want to display the relation of the columns FC and Cat. The releation is given over the the column LookUp. The problem is that for every Cat the matrix is showing me the relation to the very first value of the column FC.

Current result

Cat	FC
ETS_ABA	A
ETS_ETK	A
ETS_KKK	A
ETS_ABA	A
ETS_OII	A
ETS_KKJ	A

Expected Result

Cat	FC
ETS_ABA	A
ETS_ETK	A
ETS_KKK	C
ETS_ABA	B
ETS_OII	C
ETS_KKJ	D

Table A

Cat	LookUp
ETS_ABA	FID_1
ETS_ETK	FID_1
ETS_KKK	FID_3
ETS_ABA	FID_2
ETS_OII	FID_3
ETS_KKJ	FID_4

Table B

LookUp A	FC
FID_1	A
FID_1	A
FID_3	C
FID_2	B
FID_3	C
FID_4	D

[Fowmy · Accepted solution]

@EmPi 

Select Table A and Merge with Table B on Lookup Column.
Modify the code, including the Table. Distinct around #"Table B"

= Table.NestedJoin(Source, {"LookUp"}, Table.Distinct(#"Table B"), {"LookUp A"}, "Table B", JoinKind.LeftOuter)

 
Expand the new merged column on FC

Result

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@EmPi 

Well, in modeling, you cannot create many-to-one relationship if the one side has unique values, If you arrange your table in that way, you do the modilng 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn