LOOKUPVALUE not reporting all availabe data

[CJC_DB]

Dear all,

i have 2 tables whre i need to combine the data set. I tried to achive this by RELATED or LOOKUPVALUE but both approaches report only a part of the available data.

picture I: report table which contains the LOOKUPVALUE fxt

Spoiler

SN_OEM = LOOKUPVALUE(BOM[Merged], BOM[IAL Part Number], ANF[P/N])

the second table is containing the necessary data. As you may see the secontable is containig the required information but it is not reporting it back. Both tables are sorted by p/n

picture2: table for lookupvalues 

Does somebody have an i dear what i need to change in order to reiceive the complete data set.

Thank you and best regards!

[Greg_Deckler · Accepted solution]

@CJC_DB Try this:

SN_OEM = MAXX(FILTER('BOM',[IAL Part Number] = 'ANF'[P/N]),[Merged])

If that doesn't work then try doing a Trim operation in Power Query for P/N and IAL Part Number columns.

[SamVS · Accepted solution]

I wonder if there's perhaps some white space (like a space at the end) in either BOM[IAL Part Number] or ANF[P/N].
   

Just curious if you add a column with a formula like this, what happens?

LookupValueTest = CALCULATE(MAX(BOM[Merged]), FILTER(ALL(BOM),BOM[IAL Part Number] = ANF[P/N]))

[CJC_DB]

@Greg_Deckler @SamVS @NikhilChenna , thank you very much for your support! i followed first the solution proposed by @Greg_Deckler . Afterwards all values have been displayed. 

Thanks alot!

[NikhilChenna · Accepted solution]

Hi @CJC_DB , I think i found a solution. 

1. Go to Power Query Editor and select this [P/N] column in the first table and right click and go to transform  - > text transforms -> trim as show in the below table. And do the same for the second table [P/N] column.

2. Click close and apply.

3. Then try the lookup again it should work. I tried my self with one example and it works.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[SamVS · Accepted solution]

I wonder if there's perhaps some white space (like a space at the end) in either BOM[IAL Part Number] or ANF[P/N].
   

Just curious if you add a column with a formula like this, what happens?

LookupValueTest = CALCULATE(MAX(BOM[Merged]), FILTER(ALL(BOM),BOM[IAL Part Number] = ANF[P/N]))

[Greg_Deckler · Accepted solution]

@CJC_DB Try this:

SN_OEM = MAXX(FILTER('BOM',[IAL Part Number] = 'ANF'[P/N]),[Merged])

If that doesn't work then try doing a Trim operation in Power Query for P/N and IAL Part Number columns.