locked
returning values from data table into lookup table RRS feed

  • Question

  • I have a table of invoices that is a lookup table. And then a table of invoice and the company that issued them that is pretty much 1 to 1 (with maybe a little bit more) to the invoice table. I want to pull the name of the issuing company into the lookup table. How can I do this and/or should I? Invoices rollup to customer so eventually I would like to slice and dice issuing company by customer. Thanks. 
    Tuesday, October 25, 2016 2:01 AM

Answers

  • Is it 1 to 1 or pretty much 1 to 1, so not entirely?

    From the lookup table, you can use RELATEDTABLE to retrieve the table of row related to a specific row in the lookup table. When you're sure a column A in that table contains only one value, you can use FIRSTNONBLANK to retrieve this value.

    This function takes a column as an argument, so you would first have to map the table to the single column. You can use the SUMMARIZE function to do that:

    = FIRSTNONBLANK(SUMMARIZE(RELATEDTABLE(Table), Table[A]), 1)

    Tuesday, October 25, 2016 7:24 AM
    Answerer
  • For a given row in your invoice table, RELATEDTABLE(Table) returns the table of all rows in your invoice/customer table (which I simply called Table here) that are related to this row. The result of RELATEDTABLE contains all the columns of the invoice/customer table.

    I aim to use FIRSTNONBLANK which is kind of an aggregation function:

    FIRSTNONBLANK(Table1[Column1], <expression>)

    loops through Column1 in the current context and returns the first value of Column1 for which <expression> is not blank. I use the constant expression '1' here, meaning that I just return the first value (which is fine if you know that the column contains the same value for all rows).

    To be able to use FIRSTNONBLANK, I have to turn the (multi-column) table into a one-column table, which FIRSTNONBLANK accepts as a column. This is what SUMMARIZE does: it takes the RELATEDTABLE table, and projects only column A (probably column [Customer] in your case). In fact, it also removes duplicates from this column, but in this case that's fine.

    Wednesday, October 26, 2016 8:14 AM
    Answerer

All replies

  • Is it 1 to 1 or pretty much 1 to 1, so not entirely?

    From the lookup table, you can use RELATEDTABLE to retrieve the table of row related to a specific row in the lookup table. When you're sure a column A in that table contains only one value, you can use FIRSTNONBLANK to retrieve this value.

    This function takes a column as an argument, so you would first have to map the table to the single column. You can use the SUMMARIZE function to do that:

    = FIRSTNONBLANK(SUMMARIZE(RELATEDTABLE(Table), Table[A]), 1)

    Tuesday, October 25, 2016 7:24 AM
    Answerer
  • Well, ideally it should be 1 to 1, but there are probably some invoices in the data table that are not in the lookup table, so I'm not really sure if I have the correct data to lookup table direction. There will NOT however be duplicates, which is what I think you are trying to get at above. 

    I tried your formula and I think it worked. Can you explain a little bit more what it is doing? Specifically the summarize formula. Thanks. 

    Tuesday, October 25, 2016 4:44 PM
  • For a given row in your invoice table, RELATEDTABLE(Table) returns the table of all rows in your invoice/customer table (which I simply called Table here) that are related to this row. The result of RELATEDTABLE contains all the columns of the invoice/customer table.

    I aim to use FIRSTNONBLANK which is kind of an aggregation function:

    FIRSTNONBLANK(Table1[Column1], <expression>)

    loops through Column1 in the current context and returns the first value of Column1 for which <expression> is not blank. I use the constant expression '1' here, meaning that I just return the first value (which is fine if you know that the column contains the same value for all rows).

    To be able to use FIRSTNONBLANK, I have to turn the (multi-column) table into a one-column table, which FIRSTNONBLANK accepts as a column. This is what SUMMARIZE does: it takes the RELATEDTABLE table, and projects only column A (probably column [Customer] in your case). In fact, it also removes duplicates from this column, but in this case that's fine.

    Wednesday, October 26, 2016 8:14 AM
    Answerer