Forum Discussion

Yonatan1984's avatar
Yonatan1984
Icon for Helper I rankHelper I
4 years ago

Lookup doesn't show data in some rows

Hello everyone.

I've a model that shows the tailor made products that I need to manufacture for the costumer.

For this I've 2 tables, WORK_ORDR_TRNS (aka WOT) and LGSTC_TRNS_TIME (aka LTT).

The fields in the table WOT that contain the order number calls:

1. CUST_ORDR_NBR (format as WHOLE NUMBER - GENERAL)

2. CUST_ORDR_LINE_NBR (format as WHOLE NUMBER - GENERAL)

The fields in the table LTT that contain the order number calls:

1. DOC_NBR (format as WHOLE NUMBER - GENERAL)

2. LINE_NBR (format as WHOLE NUMBER - GENERAL)

The field that I want to show in the Table WOT call CRE_DATIME from LTT.

The formula that I used is LOOKUPVALUE but there is a problem in some rows that the data showes in WOT as blank despite there's a data in LTT for the order an line.

There's a screenshot from WOT in row without data:

There's a screenshot from LTT in the same order:

 

There's a screenshot from WOT in row with data:

This is the formula:

TIME_ORDER = LOOKUPVALUE(LGSTC_TRNS_TIME[CRE_DATIME],LGSTC_TRNS_TIME[DOC_NBR],[CUST_ORDR_NBR],LGSTC_TRNS_TIME[LINE_NBR],[CUST_ORDR_LINE_NBR])

 

7 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I suggest fully-qualifying the table names in the formula to start with i.e. put the table names in where thay are missing.

    If that doesn't work, take a specific example and hardcode the values in the formula to test whether they can be found

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Yonatan1984 ,

    By my test, haven't reproduced your problem, could you please share the error sample? It helps resolve issue faster.

    Best Regards,
    Community Support Team _ kalyj

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi,

        One useful test that I do in situations like this is to apply "clean" and "trim" steps in powerquery. It is possible there is a space or somethin like that messing with the lookup. Another thing to look out for would be the date format. Date columns data tyoes are typically the most vunerable.

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    "I share all the the sample." - we don't want to see a picture.  We can help you if you post the actual data.

    Please share a small pbix which illustrates the problem

    • Yonatan1984's avatar
      Yonatan1984
      Icon for Helper I rankHelper I
      4 years ago

      I found the solution!

      There was a table that I didn't needed, when I delete it from the model, all the data shows.

      Probebly the reletionship was'nt right.