cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ealvarado
Level: Powered On

Add the column of another table.

Hi, everyone

I got this problem that I would expect to be easy to solve but has been impossible for me to do for the past two days.
I want to add the column of another table to my main table such that:

 

{
	MainTable: [
		{
			"Names": "John Doe",
			"loginName":"john.doe",
		},
		{
			"Names": "Jane Doe",
			"loginName":"jane.doe",
		}
	]
}

{
	Hours: [
		{
			"WorkedHours": 120.00
		},
		{
			"WorkedHours": 115.50
		}
	]
}

 

becomes a single collection like this:

{
	MainTable: [
		{
			"Names": "John Doe",
			"loginName":"john.doe",
			"WorkedHours": 120.00
		},
		{
			"Names": "Jane Doe",
			"loginName":"jane.doe",
			"WorkedHours": 115.50
		}
	]
}


Collect will just add the rows. AddColumns seems incapable of taking data from other tables. Update writes over the whole row. I read that Patch could do it but that I needed an anchor column so I went to the trouble of creating a Index for those two tables with this guide http://powerappsguide.com/blog/post/generating-row-numbers.

But Patch doensn't add them. I honestly don't know what to do. 

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Level 8

Re: Add the column of another table.

@ealvarado 

I believe I can help you.  From your post I understand you have made a collection that includes an Index number for each record that looks like this.

 

MainTable
[{
    "IndexA": 1,
    "Names": "John Doe",
    "loginName":"john.doe",
},
{
    "IndexA": 2,  
"Names": "Jane Doe", "loginName":"jane.doe", }] HoursTable [ {IndexB: 1, "WorkedHours": 120.00}, {IndexB: 2, "WorkedHours": 115.50} ]

 

Here's how I would add the WorkedHours to your MainTable.  The ADDCOLUMNS is able to get values from other tables when a LOOKUP function is used in the 3rd argument.

 

AddColumns(
    MainTable,
    "WorkedHours",
    LookUp(HoursTable,IndexA=IndexB,WorkedHours)
)   

 

Let me know if you have any questions.

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

2 REPLIES 2
mdevaney
Level 8

Re: Add the column of another table.

@ealvarado 

I believe I can help you.  From your post I understand you have made a collection that includes an Index number for each record that looks like this.

 

MainTable
[{
    "IndexA": 1,
    "Names": "John Doe",
    "loginName":"john.doe",
},
{
    "IndexA": 2,  
"Names": "Jane Doe", "loginName":"jane.doe", }] HoursTable [ {IndexB: 1, "WorkedHours": 120.00}, {IndexB: 2, "WorkedHours": 115.50} ]

 

Here's how I would add the WorkedHours to your MainTable.  The ADDCOLUMNS is able to get values from other tables when a LOOKUP function is used in the 3rd argument.

 

AddColumns(
    MainTable,
    "WorkedHours",
    LookUp(HoursTable,IndexA=IndexB,WorkedHours)
)   

 

Let me know if you have any questions.

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

ealvarado
Level: Powered On

Re: Add the column of another table.

It worked! It worked! Smiley Surprised

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 348 members 6,077 guests
Please welcome our newest community members: