Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    5 years ago

    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's avatar
    AlB
    5 years ago

    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.

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.