Problems combining two collections

(1) Share

Report

Posted on by Community member

I have the following collections

ClearCollect(

colActualEventCount,

ShowColumns(

Filter(

Jobs, StartDate >= dp_SearchStartDate && StartDate <= dp_SearchEndDate.SelectedDate

Job_Id,

Band_Id_fk,

EventName,

JobClassification_Id_fk,

StartDate,

Status_Id_fk

)

);

ClearCollect(

colCeremonialBandUnitEvents,

ShowColumns(

Filter(UnitsToJobs, Value(dd_BandSelection.Selected.Band_Id) = Value(Band_Id_fk)

&& Unit_Id_fk = 149)

Job_Id_fk,

Band_Id_fk,

Unit_Id_fk,

UnitCode

);

The colActualEventCount has a one to many relationship with the colCeremonialBandUnitEvents collection.

How do I create the query that would perform an join of the colActualEventCount and the colCeremonialBandUnitEvents based on Job_Id = Job_Id_fk and return the columns Job_Id, Unit_Id, EventName, StartDate, JobClassification_Id_fk and Unit_Id_fk and UnitCode?

Also, where can you recommend go place to learn about how to develop queries for Power Apps?

Categories:

Power Query

I have the same question (0)

All responses (3)

Answers (1)

StretchFredrik 3,395 Super User 2025 Season 2 on at

Like (0)

Report
Hello,

Not sure if this is what you are looking for, but give it a shot and get back to me if there is anything you would like to do differently or if delegation warning is a problem with your datesources.

ClearCollect( colJoinedEvents, ForAll( colActualEventCount As currentActualEvent, ForAll( Filter(colCeremonialBandUnitEvents, Job_Id_fk = currentActualEvent.Job_Id) As unit, { Job_Id: currentActualEvent.Job_Id, EventName: currentActualEvent.EventName, StartDate: currentActualEvent.StartDate, JobClassification_Id_fk: currentActualEvent.JobClassification_Id_fk, Unit_Id: unit.Unit_Id_fk, UnitCode: unit.UnitCode } ) ) );

If my response solved your issue, please mark it as ✅ Accepted Answer and give it a like.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @Community member,

Offering another structure that may be also useful.
ClearCollect( colCombined RenameColumns( AddColumns( colCeremonialBandUnitEvents, EventName, LookUp( colActualEventCount As _Data, _Data.Job_Id_fk = Job_Id ).EventName, JobClassification_Id_fk, LookUp( colActualEventCount As _Data, _Data.Job_Id_fk = Job_Id ).JobClassification_Id_fk, StartDate, LookUp( colActualEventCount As _Data, _Data.Job_Id_fk = Job_Id ).StartDate, Status_Id_fk, UnitCode, LookUp( colActualEventCount As _Data, _Data.Job_Id_fk = Job_Id ).UnitCode ), Job_Id_fk, Job_ID, Unit_Id_fk, Unit_ID ) )

Was this reply helpful? Yes No
Community member 8 on at

Like (0)

Report

The second posted answer returns a table with the one column titled Value with each row having an entry of [Table].

It seems like it should be simple to combine two tables like in a simple SQL join.

Was this reply helpful? Yes No