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.

[echodapogi]

Hi Warren, 

I was able to apply it to my actual case but i have this very little issue on this particular view.

I need the Positive value to be Negative (-) and i need the negative value to be positive in the Variance column

I tried to recode it but no luck. can you please please help?

Here is the code. 

With(
   {wReceived:tblReceivedSerials},
   SortByColumns(Filter(RenameColumns(
      AddColumns(
         xlsPullbackTablePlanningList,
         "Qty",
         LookUp(
            wReceived,
            SKUReceived  = SKU_x0020__x0023_
         ).Qty,
         "Variance",
         'Qty to pullback' - 
         LookUp(
            wReceived,
            SKUReceived  = SKU_x0020__x0023_
         ).Qty
      ),
	  "Qty",
	  "Received Qty"
   ), 'Part Location Number' = txtLocationNo.Text && Variance <> 0), "Variance", Descending)
)

Qty_x0020_to_x0020pullback is the requested quantity

Received Qty is the actual received items. 

I need the two values of (-2) to (2) and (-5) to (5) (positive)

and all other values is negative. How can i do that. please help.

[WarrenBelz · Accepted solution]

@echodapogi ,

Just turn the last one around for the negative and filter as below

With(
   {wReceived:tblReceivedSerials},
   SortByColumns(
      RenameColumns(
         AddColumns(
            Filter(   
               xlsPullbackTablePlanningList,
               IsBlank(YourSearchBox.Text) || 'Location Number = YourSearchBox.Text
            ),
            "Qty",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty,
            "Variance",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty - 'Qty to pullback'      
         ),
         "Qty",
         "Received Qty"
      ), 
     'Part Location Number' = txtLocationNo.Text && Variance <> 0
   ), 
   "Variance", 
   Descending)
)

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]

Thanks WarremBelz. You're the BEST. 

[echodapogi]

Hi Warren, 

Hello again, Please help. How can i invert the positive to negative and negative to positive on this code? 

I manipulated the code you gave me but im struggling to invert the positive and negative. 

          
           
    With(
   {wReceived:xlsPullbackTablePlanningList},
   SortByColumns(Filter(RenameColumns(
      AddColumns(
         tblReceivedSerials,
         "Requested",
         LookUp(
            wReceived,
            'SKU #'  = SKUReceived && PartLoc = 'Part Location Number'
         ).'Qty to pullback',
         "Variance",
          
         LookUp(
            wReceived,
            'SKU #'  = SKUReceived && PartLoc = 'Part Location Number'
         ).'Qty to pullback' - Qty
      ),
	  "Qty",
	  "Received Qty"
   ), PartLoc =TextInput6_2.Text && Variance <> 0), "Variance", Descending)
)

The 2 should be -2 and the 1 should be -1, all other should be positive. all fours are positive

Please help. 

Thank you again.