Forum Discussion
Using Target Data from another Sheet to link to Power Query Data
Hi all,
I'm building a training dashboard and have used Power Query to pull through the data that comes directly from the learning system. I can show how many units each person, and then each department has completed. However, Different Departments, and then divisions within those departments have different mandatory competencies.
I have these set up in another sheet in my workbook as a course database. I've set it up for Course - Tier (Mandatory or not) - Department - Division. So this table shows how many mandatory units each department must complete. My issue when trying to create relationships in that there are duplicates, as some departments have a number of the same courses.
Everything else is working ok - but I need to show how many courses have been completed vs the amount of courses they need to complete each year, and in case this changes over the year (for instance if we add a new course) I want that to be dynamic instead of just saying "out of 18" for instance.
Can anyone provide any guidance? I'd greatly appreciate it!
Take this:
1. Create Unique Keys for Relationships
- To avoid issues with duplicates, you can create a unique composite key in both the Power Query dataset and your course database.
- Concatenate the fields that uniquely identify a record, such as Department + Division + Course. This ensures that each combination is unique for linking.
Power Query:
[Department] & "-" & [Division] & "-" & [Course]2. Transform Data for Aggregation
- In Power Query, consider grouping the data to summarize or aggregate it based on the required metrics:
- Mandatory Units Completed: Group data by Department or Division, and count the completed courses.
- Total Mandatory Units: Use your course database and summarize by Department or Division.
3. Link the Tables
- After creating the unique keys, merge the Power Query table with the course database using this key. This will allow dynamic updates if courses are added or changed in the future.
4. Calculate Metrics Dynamically
- In Power BI or Excel:
- Create a calculated column for the "Number of Mandatory Courses Completed" and "Total Mandatory Courses."
- Use DAX formulas (in Power BI) or standard Excel formulas to calculate the percentage completion dynamically:
Percentage Completed = (Completed Courses / Total Courses) * 1005. Handle Duplicates in Course Database
If you’re dealing with duplicates for valid reasons (e.g., a course applies to multiple divisions), consider filtering the data to remove unnecessary duplication at the report level, based on the audience's needs.
