Combining two tables with variance

[echodapogi]

Hi there, 

I am reaching out again in this forum if anybody can figure out how to combine this two tables. I'm out of ideas in my mind. 

I need a summary of what has been requested vs what has been received. I need to display it using data table in powerapps if possible. Please Help. 

Thanks in advance.

MainrequestTable 

LocationNumber	LocationName	Model	Qty
1111	Austin Location	IphoneX	3
1111	Austin Location	Samsung	4
2222	Michican Location	Nokia	5

ReceivedTable

recLocationNumber	recLocation	recModel	receivedqty
1111	Austin Location	IphoneX	2
1111	Austin Location	Samsung	2
2222	Michican Location	Nokia	3

is there a way to combine these two tables? like joining them. using data table in powerapps?

like the below summary?

Location Number	Location Name	Model	Requested Qty	Received Qty	Variance
1111	Austin Location	IphoneX	3	2	1
1111	Austin Location	Samsung	4	2	2
2222	Michican Location	Nokia	5	3	2

[WarrenBelz · Accepted solution]

HI @echodapogi ,'

This will only work properly if your Received Table items numbers in under your Delegation limit as relational lookups are not Delegable, but try this

With(
   {wReceived:ReceivedTable},
   RenameColumns(
      AddColumns(
         MainRequestTable,
         "ReceivedQty",
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty,
         "Variance",
         Qty - 
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty
      ),
	  "qty",
	  "Requested Qty"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[echodapogi]

hi warrenbelz, thank you for you reply. where i need to put this code? is the datatable items property?

[echodapogi]

Hi Warren, i figured it out how to make it work from your code. one more question is there a way to add filter on this? like filter by Location Number?

[echodapogi]

just want to say THANK YOU WARREN. it helps a lot. 

[WarrenBelz]

Hi @echodapogi ,

As an example if you had a Text Box called txtLocationNo, you would do this

With(
   {wReceived:ReceivedTable},
   RenameColumns(
      AddColumns(
         Filter(
            MainRequestTable,
            IsBlank(txtLocationNo) || StartsWith('Location Number',txtLocationNo.Text)
         ),
         "ReceivedQty",
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty,
         "Variance",
         Qty - 
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty
      ),
	  "qty",
	  "Requested Qty"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.