Forum Discussion
New Table from Two Sources
Hi, I am looking to build a new table constructed from two seperate tables with similar data. Below are the fields in the table. There are some other fields but I will not be needing them in the new table.
Table 1
User ID
Course Name
Course Completion Date
Table 2
User ID
Course Name
Course Completion Date
Everything in Table 1 needs to be moved over to the new table. Table 2 is different: there is logic behind what rows/records are brought over to this new table. Here is a very simplified example:
Table 1
| User ID | Course Name | Completion Date |
| abc123 | Course A | 1/1/2021 |
| abc123 | Course B | 1/1/2021 |
| abc123 | Course C | 1/1/2021 |
| abc123 | Course D | 1/1/2021 |
Table 2
| User ID | Course Name | Completion Date |
| abc123 | Course 1 | 1/1/2021 |
| abc123 | Course 2 | 1/1/2021 |
| abc123 | Course 3 | 1/1/2021 |
| abc123 | Course 4 | 1/1/2021 |
As stated earlier, the New Table should have all records from Table 1. Additionally, example logic: If a user has a completion record for Course A ('Table 1'[Course Name] = Course A) and has also completed Cours 1 and Course 2 from Table 2, then add the record to New Table as "Course E".
I have been researching most of the day and cannot find anything for this sort of "if/then" logic. I know there has to be a way to do this, and maybe I am overcomplicating it. Any insights would be greatly appreciated!
4 Replies
HiAnonymous
I don't quite understadnd the logic. Can you explain it a bit more and show the resulting table you would have from the two above?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
To further explain the logic: user abc123 has completion records in two tables from two seperate sources. These tables have been brought into Power BI. These records are pieces of a larger picture. In my scenario, the New Table being created would have ALL of the records from Table 1. In addition, I also will need to create new records based off logic. One example is: if a user completes "course A" from Table 1 and "Course 1" from Table 2, then create a new record in the new table for that user with a new name (Course E in this case) and the date from Table 2. The date is nice to have but not necessary if it overcomplicates.
Do you have just the one scenario/logic to replace? Or multiple? Either way, it might be easier to just append the tables and work out the logic in a measure. How do you plan to analyze/visualize the data once you get the table you propose?
Regards,
Pat
I was able to limit the tables enough to append them. I appended as new. I do have a user table, so my thought is to add a column to the user table that would look at user_table[user_id] and check the merged table if this user meets the qualifications. So example:
I am now struggling creating the three columns in the user table. I thought maybe I could try:
A+B Trained = if(COUNTAX(FILTER('Merged Data','All User Data'[user_id]),'Merged Data'[course_name] = "Course A") = 1 && COUNTAX(FILTER('Merged Data','All User Data'[user_id]),'Merged Data'[course_name] = "Course B") = 1, "YES", "NO")But I get an error saying "Cannot convert value 'abc1234' of type Text to type True/False. Any thoughts? I am so close to having this done.
