Add unique values to a column retrieved from multiple tables in PowerBI

0
votes

I currently have two tables (Excel sheets) related to each other in PowerBI:

  1. Inventory; columns (Article number, description, quantity, sumOfQuantityReceived)

  2. MaterialsReceived; columns (Article number, quantityReceived, DateReceived)

The tables are related to each other with an one (Inventory) to many (materialsReceived) relationship, as shown below.

Relationship

However, the Inventory table currently only shows the Article numbers that are present in the Inventory table and will not automatically add a new row with article number if there is a new one present in the MaterialsReceived table.

For example: The inventory list currently contains the following information

Inventory list

While there is a new article number present in the MaterialsReceived table (article number: 969686)

Materials Received

So my question is now: How can I create a new table in PowerBI that retrieves the unique article numbers from both tables and adds them to a new column.

In this situation, the new table would contain one column with 4 rows (456982, 456987, 556987 & 969686)

1
excelpowerbipowerbi-desktop

1 Answers

1
votes

You can try below code

Uniq_Article_Number_Table =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( inventory[article number] ),
            VALUES ( 'Material Received'[article number] )
        )
    ),
    [article number] <> BLANK ()
)

code snippet