cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Josh_BI_UK Regular Visitor
Regular Visitor

Compare Two Tables - Repeat Rows in Table A for each Name in Table B

Hi PWR BI Family ,

 

I would like to create a report for managers, so that they can check which staff have missing timesheets.

 

I would like to know for every [WeekStart] in my WeekStartDatesTbl; if a staff member has submitted their timesheet or not.

 

I have three tables to start with and I would like to create a fourth.

 

Find the data and tables here: PWRBI_missingtimesheets.pbix

 

Starting tables: 

Table A: WeekStartDatesTbl

Table B: StaffNamesTbl

Table C: TimesheetsTbl

 

timesheet_tables.pngCurrent Tables

 

 

 

 

 

 

 

 

 

 

 

 

 

I would like hard data (i.e. not the output of a formula). I would prefer that the solution leverage PowerQuery and create a new table (Table 😧 MissingTimesheetsTbl. However if my PWR BI Family ❤ thinks otherwise; no worries, I’ll go with a DAX solution.

 

I envision a table as follows:

 

StaffMemberName column: a list of staff names (derived from the staff table) in column A.

Week Start Dates: Week start dates in column B (dates start on Monday, and would be repeated for each staff member)

 

TimesheetSubmittedYN column: a look up against the Timesheet table, which checks if for Staff member (e.g. Jo Name) a row exists for the corresponding week start date. If a timesheet exist, out "Yes", if no timesheet exist out "No", if the date is in the future i.e. date greater than now() + 7 days out put "Pending"

 

TimesheetID Column: the ID number of the timesheet from the timesheet table, if none exist output “Missing Sheet”; if the date is in the future i.e. date greater than now() + 7 days output "Pending".

 

 

timesheet_missing.pngI would like to create Table 😧 MissingTimesheetsTbl

 

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: Compare Two Tables - Repeat Rows in Table A for each Name in Table B

I had a little play around with this.

I created a new table

AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)
That gives you all the possible staff timesheets. You then have to link up with the existing
timesheet table to find which rows don't have data.
I created a unique key
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]
and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing],  "YYYYMMDD") & TimesheetsTbl[StaffMemberName]
You then create a relationship between them, on this field.
You can then construct a table visualisation with all the timesheet dates, staff, and
timesheet ID. Missing ID's will show where there is no timesheet

View solution in original post

2 REPLIES 2
HotChilli New Contributor
New Contributor

Re: Compare Two Tables - Repeat Rows in Table A for each Name in Table B

I had a little play around with this.

I created a new table

AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)
That gives you all the possible staff timesheets. You then have to link up with the existing
timesheet table to find which rows don't have data.
I created a unique key
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]
and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing],  "YYYYMMDD") & TimesheetsTbl[StaffMemberName]
You then create a relationship between them, on this field.
You can then construct a table visualisation with all the timesheet dates, staff, and
timesheet ID. Missing ID's will show where there is no timesheet

View solution in original post

Josh_BI_UK Regular Visitor
Regular Visitor

Re: Compare Two Tables - Repeat Rows in Table A for each Name in Table B

This is a good solution @HotChilli  thank you. Could I possibly leave this problem open in favour of a PowerQuery solution. I would really like the cross joined table to be created at run time as physical table.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,225)