Forum Discussion

ells69's avatar
ells69
Icon for Helper I rankHelper I
3 years ago

alternative to merge to retrieve description fields

In M 
I have a fact query with many keys in. Lets Call it "Fact". I have several querys that have the matching description

LKUP_Product which consists of [ProductID], [ProductName],[Product Colur]
LKUP_Customer which consists of [CustomerD], [CustomerName]

 

I do not want to Merge the lookups. Is there a way of using
list.Buffer to get a list of ProductID, ProductName
Then a way to compare Fact.ProductID to List of Products to get the ProductName

 

I am sure I have seen this before but cant find my notes or the example again

 

Many thanks

Need Help
Tips and Tricks

3 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    Hi, ells69 

    1. Create a record of products as p_rec = Record.FromList(LKUP_Product[ProductName], LKUP_Product[ProductID])

    2. add new column to Fact (or transform existing) Table.AddColumn(Fact, "ProductName", each Record.FieldOrDefault(p_rec, [ProductID], "none"))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    If you're going to be loading these tables to a data model, you can just use a one to many relationship and link them that way. Otherwise I would add a custom column using each Table.FindText.

     

    --Nate

  • ells69's avatar
    ells69
    Icon for Helper I rankHelper I
    3 years ago

    Think I will try the solution from AlienSx  . This is kind of what I am looking for,. My users are a little crazy and we may need to have these links across multiple tables. I like to keep the data model as simple as possible. That way there is less chance of another Dev making a mess or the user selecting unrelated items (this happened yesterday and it is hard to explain to the user why there is an issue) 
    Thanks both

    M