Forum Discussion
LOOKUPVALUE not reporting all availabe data
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
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!
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.
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]))
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!
4 Replies
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.
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]))
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!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!
