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 dep...
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) * 100
5. 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.
