Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
I currently have two tables (Excel sheets) related to each other in PowerBI:
Inventory; columns (Article number, description, quantity, sumOfQuantityReceived)
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 |
Solved! Go to 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
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. |
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. |
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