Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
New_User_2022
New Member

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 2
arvindsingh802
Super User
Super User

Can you share sample data of both tables as well as expected result
Do not share any sensitive data


If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
Proud to be a Super User!!

Absolutely, 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.

EmployeeDateother data…
Employee121/11/2022data here
Employee321/11/2022data here
Employee421/22/2022data here
Employee114/11/2022data here
Employee107/11/2022data here
Employee207/11/2022data here
Employee307/11/2022data here
Employee407/11/2022data 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

Employeeother data…
Employee1data here
Employee2data here
Employee3data here
Employee4data here
Employee5data here

 

Expected output table: TRUE / FALSE for submissions by employee / week

EmployeeWeekSubmission
Employee121/11/2022TRUE
Employee221/11/2022FALSE
Employee321/11/2022TRUE
Employee421/11/2022TRUE
Employee521/11/2022FALSE
Employee114/11/2022TRUE
Employee214/11/2022FALSE
Employee314/11/2022FALSE
Employee414/11/2022FALSE
Employee514/11/2022FALSE
Employee107/11/2022TRUE
Employee207/11/2022TRUE
Employee307/11/2022TRUE
Employee407/11/2022TRUE
Employee507/11/2022FALSE

 

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:

EmployeeWeekSubmission
Employee221/11/2022FALSE
Employee521/11/2022FALSE
Employee214/11/2022FALSE
Employee314/11/2022FALSE
Employee414/11/2022FALSE
Employee514/11/2022FALSE
Employee507/11/2022FALSE

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors