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

[Anonymous]

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.

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

Article number	Description	Quantity
456982	Product 1	2
456987	Product 2	1
556987	Product 3	3

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

Article number	QuantityReceived	DateReceived
456982	1	10-2-2020
456982	2	12-2-2020
969686	1	14-2-2020

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 such as:

NEWTABLE

Article number

456982

456987

556987

969686

[TomMartens · Accepted solution]

Hey @Anonymous ,

I use this DAX statement to create a table that contains "unique" values from multiple tables:

New Table = 
DISTNCT(
    UNION(
        ALLNOBLANKROWS( 'table1'[columname] )
        , ALLNOBLANKROWS( 'table2'[columname] )
    )
)

Hopefully, this provides what you are looking for.

Regards,

Tom

[AlB · Accepted solution]

Hi @Anonymous 

See it all at work in the attached file.

You can create a calculated table in DAX:

Option1 =
DISTINCT (
    UNION (
        DISTINCT ( InventoryT[Article number] ),
        DISTINCT ( MaterialsReceived[Article number] )
    )
)

or create a table in the query editor. Place the following M code in a blank query to see the steps.

let
    T1 = Table.SelectColumns(InventoryT, "Article number"),
    T2 = Table.SelectColumns(MaterialsReceived, "Article number"),
    res_ = Table.Distinct(Table.Combine({T1,T2}))
in
    res_

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[TomMartens · Accepted solution]

Hey @Anonymous ,

I use this DAX statement to create a table that contains "unique" values from multiple tables:

New Table = 
DISTNCT(
    UNION(
        ALLNOBLANKROWS( 'table1'[columname] )
        , ALLNOBLANKROWS( 'table2'[columname] )
    )
)

Hopefully, this provides what you are looking for.

Regards,

Tom