Lookupvalue not working? Works some times but not all times

[Anonymous]

Hi there,

Am using lookupvalue to determine the state for stores in a data table that I have.  The stores are associated to states via store id.

The problem i am having is that it works sometimes but not all times.  Take a look at the picture below.  It returns florida (FL)for some of the data but not all of it.  I checked in the reference table and there are no duplicates that map to a blank state, I have no idea why this is happening.  

Anyone else run into this?

[orsunil]

Hi , I have similar problem, anyone have solution for this?

[jdbuchanan71]

@Anonymous 

Is it possible that the [Store ID] that are not pulling a [State] back have non-printing special characters or decimal places that are hidden by the format of the column?  PowerQuery has a lot of text cleaning options (CLEAN, TRIM, etc) that you could apply before the data gets loaded to the model.  

If possible you should follow @parry2k suggestion of setting a relationship between the two tables though.

[Anonymous]

Hi there, no i had extracted the data into excel and checked to see if there were any leading spaces etc.

Apparently it has to do with a Many to Many relationship that I have on state with another table.  That is what is causing this.  When I extract the same table into a new table that has no connections/relationships I can get state to populate for all stores.

[Jon_vB]

Thanks for this!   I had the same issue - checked that the type for both columns was Integer, and was ready to pull my hair out.   For me - it was a one-to-one relationship that was breaking this --- which seems really odd to me!  but - I made that relationship inactive, and then it worked fine.  Weird.

[parry2k]

@Anonymous based on the information you provided earlier, it looked like it was one to many relationship. In case of many to many, yes you are going to get different results. You need to decide what business logic you want to implement when  more than one record is found.

[parry2k]

@Anonymous the data type of home_store_id and store_id is same? I believe it is same but want you to double check.

[Anonymous]

HI there, Datatypes are the same, checked that...

[parry2k]

@Anonymous not sure, just wondering why you are using lookup function, why not set relionship between both the table on these columns, and you can use related function to get value.