UNIION tables where I have identical data rows

[swe_sebastian]

I want to UNION two tables into a third.

I have one table with open orders and one table with invoiced orders and I would like to get all this info in the same table.

My problem is that I have identical information in one of the original tables.

For example one customer has the same product with the same qty on two rows in the order. When I try to UNION them into the new table I get only one of the rows.

This is the formula I use to create the table "OrderInvoice":

OrderInvoice = 

(

UNION(

ALL(inorder[Customerid], inorder[Prodcode], inorder[qty]),

ALL(invoice[Customerid], invoice[Prodcode], invoice[qty])

)

)

[Ashish_Mathur]

Hi,

You may want to use the Query Editor to append date from both tables into one.  This way the duplicae rows will be maintained.

[parry2k]

@swe_sebastian try this

OrderInvoice = 

(

UNION(

SELECTCOLUMNS ( inorder, "CustomerId", inorder[Customerid], "ProdCode", inorder[Prodcode], "Qty", inorder[qty]),

SELECTCOLUMNS ( invoices, "CustomerId", invoice[Customerid], "ProdCode", invoice[Prodcode], "Qty", invoice[qty])

)

)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[swe_sebastian]

This gives me unfortunately the same result. The duplicate rows are not with me.

[lbendlin · Accepted solution]

Add an index column to your tables before doing the Union.

[swe_sebastian]

Hi. Now I understand what you mean. I added the Index in the two tables, imported the Index in the UNION and now it works!

Easy and I should have thought about that.

Thanks!

[swe_sebastian]

Could be a soultion, but I don't have access to the original data. I just import the data from the two tables. Right now I can't modify the data in the original tables.