Forum Discussion
Help Creating merged table with all potential combinations in rows
Hi all, I've been tasked with creating a merged table with PBI premium which takes: a table featuring date values and employee values (showing employees who have submitted information on specified dates). The dates are always Mondays for the last 6 weeks, so always 6 unique date values. and another table featuring all employees who should be submitting information. The table is updated as employees come and go I need to create a table which will allow me to track who isn't sending information through. My idea is to create a merged table which creates a row for every possible employee / date combination then a boolean column to show if such a record set exists in the first table. Is this the best way to consider this task or is there an easier way? If there isn't an easier way, how would I go about building this table?
2 Replies
Can you share sample data of both tables as well as expected result
Do not share any sensitive dataAbsolutely, Sample data below:
Sample table 1: submissions by date / employee (plus some additional columns of information not relevant to this task):
note: dates are always the last 6 mondays.
note: there are over 150 employees in the real dataset.
Employee Date other data… Employee1 21/11/2022 data here Employee3 21/11/2022 data here Employee4 21/22/2022 data here Employee1 14/11/2022 data here Employee1 07/11/2022 data here Employee2 07/11/2022 data here Employee3 07/11/2022 data here Employee4 07/11/2022 data here Sample Table 2: A list of all employees submitting data (plus some supplementary columns not relevant to this task):
note: each employee only has one record in this table, there are no duplicates
Employee other data… Employee1 data here Employee2 data here Employee3 data here Employee4 data here Employee5 data here Expected output table: TRUE / FALSE for submissions by employee / week
Employee Week Submission Employee1 21/11/2022 TRUE Employee2 21/11/2022 FALSE Employee3 21/11/2022 TRUE Employee4 21/11/2022 TRUE Employee5 21/11/2022 FALSE Employee1 14/11/2022 TRUE Employee2 14/11/2022 FALSE Employee3 14/11/2022 FALSE Employee4 14/11/2022 FALSE Employee5 14/11/2022 FALSE Employee1 07/11/2022 TRUE Employee2 07/11/2022 TRUE Employee3 07/11/2022 TRUE Employee4 07/11/2022 TRUE Employee5 07/11/2022 FALSE My only other note is that an employee can and usually will submit more than one record of information per date. But here we're only interested in flagging if an employee has submitted anything at all. The final output for reporting would filter out any TRUE values, leaving us with this:
Employee Week Submission Employee2 21/11/2022 FALSE Employee5 21/11/2022 FALSE Employee2 14/11/2022 FALSE Employee3 14/11/2022 FALSE Employee4 14/11/2022 FALSE Employee5 14/11/2022 FALSE Employee5 07/11/2022 FALSE
