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

Add Column to Collection by lookup in another Collection

I have two collections:

(CollectionA) with Columns:

- ID

- Description

 

(CollectionB) with Columns

- Id

- Price

 

I would like to create additional Column in CollectionA "Price" and bring in the Value from Price in CollectionB based on the lookup of of CollectionB.Id=CollectionA.ID

 

Any help or direction is greatly apprecated!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Add Column to Collection by lookup in another Collection

Hi @ruslank ,

Do you want to add a new column in your Collection A with values from the Price column in your Collection B based on CollectionB.Id=CollectionA.ID?

 

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

Set OnSelect property of the "Button" control to following:

ClearCollect(
               NewCollectionA,
               AddColumns(
                           CollectionA,
                           "Price",
                           LookUp(CollectionB, Id = CollectionA[@ID], Price)
               )
)

Note: The result the AddColumns() function returned is a new table with the transform applied. The original table isn't modified. In other words, the AddColumns() function returns a temporary table with the transform applied, but the underlying table (CollectionA) would not be modified.

If you want to get/use the modified table, you could consider save the result the AddColumns() function returned into a new Collection as I mentioned above, then you could use the NewCollectionA collection within your app.

 

More details about the AddColumns function, please check the following article:

AddColumns function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Administrator
Administrator

Re: Add Column to Collection by lookup in another Collection

Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?

 

Thank you,

 

@TopShelf-MSFT 

5 REPLIES 5
sopatte
Level 8

Re: Add Column to Collection by lookup in another Collection

You can't dynamically add Columns to an existing collections afaik and write them back to that collection. But you could save a new collection with the additional column:

 

Collect(
    CollectionC,
    AddColumns(
        CollectionA,
        "Price",
        Lookup(
            CollectionB,
            Id=ID,
            Price
        )
    )

)

If you run into a naming collision with the ID column, use RenameColumns, e.g. instead of using CollectionB use:
RenameColumns(CollectionB,"Id","BID")
which will produce a table identical to CollectionB but with the Id column renamed to BID.



Community Support Team
Community Support Team

Re: Add Column to Collection by lookup in another Collection

Hi @ruslank ,

Do you want to add a new column in your Collection A with values from the Price column in your Collection B based on CollectionB.Id=CollectionA.ID?

 

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

Set OnSelect property of the "Button" control to following:

ClearCollect(
               NewCollectionA,
               AddColumns(
                           CollectionA,
                           "Price",
                           LookUp(CollectionB, Id = CollectionA[@ID], Price)
               )
)

Note: The result the AddColumns() function returned is a new table with the transform applied. The original table isn't modified. In other words, the AddColumns() function returns a temporary table with the transform applied, but the underlying table (CollectionA) would not be modified.

If you want to get/use the modified table, you could consider save the result the AddColumns() function returned into a new Collection as I mentioned above, then you could use the NewCollectionA collection within your app.

 

More details about the AddColumns function, please check the following article:

AddColumns function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Administrator
Administrator

Re: Add Column to Collection by lookup in another Collection

Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?

 

Thank you,

 

@TopShelf-MSFT 

ruslank
Level: Powered On

Re: Add Column to Collection by lookup in another Collection

Thank you so very much apologies for delay in my reply @v-xida-msft ! This works! Woudl you be able what the difference is between CollectionA.ID and CollectionA[@ID]

Thank you!

ruslank
Level: Powered On

Re: Add Column to Collection by lookup in another Collection

@v-xida-msft @TopShelf-MSFT Team, do you by chance have any insight into the question i posted on the difference  between CollectionA.ID and CollectionA[@ID]? Thank you much!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 297 members 4,313 guests
Please welcome our newest community members: