Answered

powerapps Joining tables with multiple records

(1) Share

Report

Posted on by Anchal_D

Hi, I am trying to join 2 tables in powerapps and trying to get the true inner join data. below is my sample data -

suppose user placed an order with multiple products, I want to join 1 record from Order with Multiple records from product table.

Table Orders -

OrderID, OrderDate

1, 2018-01-21

2, 2017-01-31

Table products -

ProductName, orderID

ABC, 1

CDE, 1

SDW,1

LOJ,2

Final Result -

Order ID, OrderDate, ProductName

1,2018-01-21,ABC

1,2018-01-21,CDE

1,2018-01-21,SDW

2, 2017-01-31, LOJ

I tried Addcolumns using lookup but it only brings one value, is it possible in powerapps or do I need to go to View route?

Thanks in Advance

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

RezaDorrani 12,145 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anchal_D

AddColumns(ProductsTable,"OrderDate",LookUp(OrdersTable,OrderID=orderID).OrderDate)

where ProductsTable = name of products table
OrdersTable = name of orders table
OrderID = column in orders table
orderID = column in products table
OrderDate = column in orders table

Regards,
Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!

From what I can tell the relationship between your tables is one order with many different products. If this is the case could do a left table join instead of an inner join (products -> orders).

ClearCollect(myJoinedData,
AddColumns(Products,"OrderDate",LookUp(Orders,OrderId=Products[@OrderId],OrderDate)))

The resulting table will look like this...

—
Please Accept as Solution if this post answered your question so others can find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Anchal_D ,

Do you want to join the Orders Table and Products Table based on Order ID value?

Could you please share a bit more about the AddColumns formula you used in your app?

I have made a test on my side, please consider take a try with the following workaround:

Add a Data Table control within your app, set the Items property to following:

AddColumns( ProductsTable, /* <-- Type ProductsTable here rather than Orders Table */ "OrderDate", LookUp(OrdersTable, OrderID = ProductsTable[@orderID], OrderDate) )

then enable corresponding columns within the Data Table control.

More details about the Disambiguation operator (@) in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#disambiguation-operator

Best regards,

Was this reply helpful? Yes No