cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Join 2 datasources

Hi everyone, I'm building a PowerApps application in which I need to join 2 Excel tables(Table 1 and Table 2 down below) together based on 2 columns(Employee and Week of). The idea is that the app will firstly loop over those rows with the same employee value and week of value then string manipulate to produce a row like this. I'm thinking about using groupby function in Powerapps. Does anyone have any suggestion? Thanks you in advance.

 

Hi everyone, I was able to find a solution so thank you everyone. I posted the solution at the bottom. 🙂

Table3.PNG

 




Table1.PNG

 

 

Table 1

 

Table2.PNG

 

 

 

Table 2

1 ACCEPTED SOLUTION

Accepted Solutions

Thank you, I found another solution. 
ClearCollect(
achievementsCollection,
GroupBy(
Achievement_Table,
"Week_x0020_of",
"Employee",
"AchievementGrouped"
)
);
ClearCollect(
plannedWorksCollection,
GroupBy(
PlannedWork_Table,
"Week_x0020_of",
"Employee",
"PlannedWorkGrouped"
)
);

To merge/link these 2 tables, I use AddColumns and LookUp

ClearCollect(GoodNewsCollection,AddColumns(achievementsCollection,"PlannedWorkGrouped",LookUp(plannedWorksCollection, Employee=achievementsCollection[@Employee] And Week_x0020_of=achievementsCollection[@Week_x0020_of],PlannedWorkGrouped)))
;

View solution in original post

4 REPLIES 4
Resolver I
Resolver I

Create local collection for joining and process like,

 

1. Clearcollect(local_colleect, table1)

2. Collct(local_colleect, table2)

 

Now u use local_colleect as a joined table. 

 

Additional note: excel will not support delegation,  ensure limited number of rows 

@MichaelNguyen ,

For the collection to work, you also need to line up the columns, so you would do this

ClearCollect(
   col1,
   ShowColumns(
      Table1,
      "Employee",
      "WeekOf"
   ),
);
Collect(
   YourCollectionName,
   col1,
   ShowColumns(
      Table2,
      "Employee",
      "WeekOf"
   )
)

 

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.

Thank you, I found another solution. 
ClearCollect(
achievementsCollection,
GroupBy(
Achievement_Table,
"Week_x0020_of",
"Employee",
"AchievementGrouped"
)
);
ClearCollect(
plannedWorksCollection,
GroupBy(
PlannedWork_Table,
"Week_x0020_of",
"Employee",
"PlannedWorkGrouped"
)
);

To merge/link these 2 tables, I use AddColumns and LookUp

ClearCollect(GoodNewsCollection,AddColumns(achievementsCollection,"PlannedWorkGrouped",LookUp(plannedWorksCollection, Employee=achievementsCollection[@Employee] And Week_x0020_of=achievementsCollection[@Week_x0020_of],PlannedWorkGrouped)))
;

View solution in original post

@MichaelNguyen 

Actually the same solution - I was simply trying to convey to you the concept of what you had to do.

 

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.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (65,743)