Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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 numberDescriptionQuantity
456982Product 12
456987Product 21
556987Product 33

 

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

Article numberQuantityReceivedDateReceived
456982110-2-2020
456982212-2-2020
969686114-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

 

 

 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

AlB
Community Champion
Community Champion

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_

 

 

 

 

SU18_powerbi_badge

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.

 

View solution in original post

2 REPLIES 2
AlB
Community Champion
Community Champion

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_

 

 

 

 

SU18_powerbi_badge

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!