I have an app that uses multiple datasources.
On startup I want to collect all the fields in 'STUDENT DEMOGRAPHIC INFORMATION' and add a field called Absences from the ADAreport source.
The common fields are as follows:
'STUDENT DEMOGRAPHIC INFORMATION' key column is OtherID
'ADAreport' key column is Title
Thx community for assisting with this code.
Hi @slhangen Try this for a start - Free-typed so I cannot test it presently
Clear(colMyCollection); ForAll( 'STUDENT DEMOGRAPHIC INFORMATION' As Student, Collect( colMyCollection, AddColumns( Student, "MyColumnName", Lookup( ADAreport, Title=Student.OtherID ).Absences ) ) )
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.
Quite a bit of red here.
Can you elaborate on the blue part:
ForAll( 'STUDENT DEMOGRAPHIC INFORMATION' As Student Collect(ColAddAttendance, AddColumns( Student, "Absences", Lookup( ADAreport, Title=Student.OtherID ).Absences ) ) )
Also customized some field names (ColAddAttendance, Absences) in orange
Hopefully we are getting closer though
@WarrenBelz typing freeform...it's our Achilles heal!!
This bit of formula should do what you need. This is based on the assumption that there is one row in your Excel with Title equaling the OtherID in the Student Demographic Information datasource. I also assumed (just based on the naming) that Title was a text column and OtherID is a numeric.
ClearCollect(colMyCollection, AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student, "Absences", Lookup( ADAreport, Title=Text(Student.OtherID) ).Absences ) )
So, if the above assumptions are all correct, this formula should give you what you want.
IF you have multiple rows in your excel file, then this would be the formula:
ClearCollect(colMyCollection, AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student, "Absences", Sum( Filter(ADAreport, Title=Text(Student.OtherID), Absences ) ) )
Let's see where that takes you.
Thanks for taking a look Randy.
Just to recap:
With re: to your provided code, even after i removed the text container tag, I have lots of red in the code (see below)
Thx again, Randy!
As @WarrenBelz mentioned...we type these things free-form, so you always have to do some due diligence on any typos we make. In this case, the Lookup should be LookUp.
Please consider this formula:
ClearCollect(colMyCollection, AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student, "Absences", LookUp( ADAreport, Title=Text(Student.OtherID) ).Absences ) )
My apologies, fellas! Still a relative novice trying to get a handle on the verbiage.
Not sure if I have the right and left tables coded correctly. Student Demographic Information is the main table, I just want to borrow the column named DAYS ABSENT from excel sheet ADAreport and add it to the columns of Student Demographic Information into a new collection.
Hopefully we are getting closer but I am still pretty perplexed:
Only a little red now: Can you identify the error?
Hi @slhangen ,
@RandyHayes is probably asleep right now and his solution is correct based on the information you have provided (so please accept his once this is sorted).
Something is not correct in either the field names or types as if they were both plain text (Single line of Text in SharePoint and formatted as text in Excel), then you would not have that error.
Please look at both values with this in mind and see if you can detect the issue.
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.