Unanswered

Joining tables in a collection results in 2 duplicate columns

(0) Share

Report

Posted on by AB48

Having a hell of a time figuring out what I'm doing wrong... I have a formula that joins 2 CDS entities using AddColumns and a lookup function. Yesterday it was working fine... but today it results in 2 duplicate columns being added to the collection. Is this just a temporary bug? Anyone else experiencing this?

Syntax is:

ClearCollect(TestColl, AddColumns(Entity1, "JoinedEntity", LookUp(Entity2, ID=Entity2[@ID])))

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

v-yuxima-msft on at

Like (0)

Report
Hi @AB48 ,

Do you want to join 2 tables by the ID?

Could you please try to change your formula expression as:

ClearCollect(TestColl, AddColumns(Entity1, "JoinedEntity", LookUp(Entity2, ID=Entity2[@ID]).FieldName))

Screenshots(please check the attachments)

20190322Entity1.PNG

20190322entiti2.PNG

20190322join2tabl...

Was this reply helpful? Yes No
PEMITServices 10 on at

Like (0)

Report

I have the same type of behavior happening, with a couple of examples:

1) On Premises SQL Data Source: Simple add column exactly as original post, column doubles, even happens in a brand new app with single expressions used to ensure that I didn't have a conflict present.
2) SharePoint Online - Load a list, all the table result columns, such as Author, are doubled and this happens with a simple ClearCollect from the list.

Was this reply helpful? Yes No
AB48 29 on at

Like (0)

Report

@v-yuxima-msft Tried your solution but it results in just the ID field of Entity2 being added Entity1, instead of the entire Entity2 being added to Entity1.

Was this reply helpful? Yes No
PEMITServices 10 on at

Like (0)

Report
Here's an example of the sharepoint list collection. This is loaded with simple
ClearCollect(colEmpDir,SharePointList)

Was this reply helpful? Yes No
PEMITServices 10 on at

Like (0)

Report

@v-yuxima-msft @AB48 , due to several examples across multiple data sources this is a bug in PowerApps at the moment.

This is very frustrating and needs resolved as soon as possible. Loads take longer, development time is increased and rather painful, also some expressions and routes to add columns error due to the column already existing.

Was this reply helpful? Yes No
PEMITServices 10 on at

Like (0)

Report
My sql on-premises data source presents this behavior with the following expressions:

ClearCollect( colOofTemp, AddColumns( '[dbo].[oofMastView]', "nextduedt", If( opcurrent <= opquoting, "QUOTING", If( opcurrent = opquoting || opcurrent = opqtflwup, "CSTPOSO", If( opcurrent >= opcstposo, "FINAL", "UNKNOWN" ) ) ) ) ); ClearCollect( colOof, AddColumns( colOofTemp, "RtgRules", If( IsEmpty( Filter( colRtgRules, fpro_id = CurrentWC ) ), Filter( colRtgRules, IsBlank(fpro_id) ), Filter( colRtgRules, fpro_id = CurrentWC ) ) ) );

Was this reply helpful? Yes No
AB48 29 on at

Like (0)

Report

@PEMITServices Glad to see I'm not alone.

To add to your list of problems your experiencing: when I try to make modifications to the collection via Patch or others, I get errors saying the referenced column doesn't exist and the error message references a different column than the one I am targetting in the syntax... very frustrating indeed.

Was this reply helpful? Yes No
AB48 29 on at

Like (0)

Report
Well here's an update... AddColumns with LookUp isn't working at all anymore. It joins the wrong row (the first row in the datasource to all items instead of the one that matches the lookup). Tried it with a basic collection and it still doesn't work. Here's the syntax I used:

ClearCollect(Coll1, {name: "1"}, {name: "2"}); ClearCollect(Coll2, {name: "1", value: "1 - test"}, {name: "2", value: "2 - test"}); ClearCollect(CollCombined, AddColumns(Coll1, "combined", LookUp(Coll2, name=Coll2[@name])))
Tried it with joining individual columns instead of the whole collection and it's still wrong. Syntax below. See attached screenshot.
ClearCollect(Coll1, {name: "1"}, {name: "2"}); ClearCollect(Coll2, {name: "1", value: "1 - test"}, {name: "2", value: "2 - test"}); ClearCollect(CollCombined, AddColumns(Coll1, "combinedName", LookUp(Coll2, name=Coll2[@name]).name, "combinedValue", LookUp(Coll2, name=Coll2[@name]).value))

Screen Shot 2019-...

Was this reply helpful? Yes No
PEMITServices 10 on at

Like (0)

Report

Since no one every acknowledged this was an issue here, I guess I'll go ahead and report that it appears to be resolved.

Was this reply helpful? Yes No