Join two tables

[Anonymous]

HI All, 

I trying to bring together two tables in my report.

Table 1 (Tells us quantity of steel which currently in production) - For a single Order #, it has quantity/weight at different stations# in steelmaking process, e.g

Station A = 100 tons

Station B   = 200 tons ,etc.

Table 2 (It is an order book , contains all orders whether the production has started on order or not)  - Contains a single row information for the order # in table 1. The tabe has information like customer, length, width, customer name, total quantity ordered by the customer for the particular order #. So unlike Table 1 it will have the total quantity of an order (100+200+etc.)

Further, It will also have information including delivery date and quantity ordered on those orders which is yet to enter production.

What I want to achieve :

1. I want to append both the table such that entire table 1 is there and fetch those orders from table 2 which are not there in Table 1
2. Since table 1 does not have dimension details of the order, I need to fetch that information from table 2

I am badly confused how to join these tables and which table should be primary . Will it be better to create a new table altogether?

Model view

Thanks.

[v-kelly-msft]

Hi  @Anonymous ,

Could you pls provide some sample data for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi , Just wanted to check is the one-drive link is working?

[v-kelly-msft]

Hi @Anonymous ,

To get the details of the order,you could create calculated columns in Table1 using LOOKUPVALUE function one by one,such as below:

_cust PO = LOOKUPVALUE('Table 2'[CUST_PO],'Table 2'[Sales_ORD_HEAD_NO],'Table 1'[SalesOrderId],blank())

And you will see:

As for appending table,what's your expected output?Just adding the orders to Table1 or adding the details together to Table1?

If you just wanna show it in a table visual,you could easily achieve it by add the salesorder from Table 2 and other information from table 1 to the visual once you have created the relationship between the 2 tables.

For details,pls check the .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for the late reply,I'm testing your data,once I work it out I will give you an update.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

HI ,

Here is the one drive link for PBi and Excel sample data.

https://1drv.ms/u/s!AoEAHRm368NsbPz_rLGY93njONs?e=WWqAPC

Thanks.