Looking for some help to look up and populate a field one a list form data stored in another . I think I need a Power Automate but need a hand getting started :)
Background - I run the L&D department and have a training management system to track staff training and Learning & development efforts. The TMS System is essentially a SharePoint Site add on and utilises SharePoint lists and integrates well with Microsoft and I'm now looking to report using Power BI.
Scenario - I have a list of 'Learners' which are the staff members - and I have extended this list with fields such as Business Unit (BU), Team and the Product they work on. When a staff member has completed a course, a separate list is populated called 'Achievements' that brings through the Learner, the course, achievement date etc.
It is this Achievement list that I use for Power BI as I get the date, the course, the skills achieved etc.
The missing link is i'd like to also bring through the BU, Product and Team column off the Learner list onto this 'Achievement' view, so that when I bring the list into Power Bi and can report on training achievements by department.
If I think about excel, I could do this as a Vlookup but unsure how to achieve this in Sharepoint list. I've seen I can make a look up column to look up from another list - but it is user choice it just presents the options as a drop down and need manually updated.. However what I'd like is that if an achievement is entered for Bob Bloggs, and we can see on the Learner list that he's in the Finance (BU) - Accounts Payable (Team) and works on Quickbooks (Product), I would like those columns to auto populate.
I hope I have explained that well enough with showing you visually! Any help on how this can be done.... calculated field referencing other lists... Power Automate .. maybe i can do the look up on Power Bi.?? anything would be greatly appreciated!
I'm self taught on Power Automate/Excel and Bi and although I pick alot of things up, I need a starting point to help me understand! :)