Multiple Fact Tables Reconciliation

[jodie_l]

I'm a novice at PowerBI and I have three tables of data that I need to compare/reconcile. Each table contains a list of dates with different data fields that I need to essentially consolidate to create a complete list of dates - filtered between two dates (I have a Date Dimensions table that lists every date available) and for a particular worker_ID.

Table 1	Table 2	Table 3	Table 4
Worker_ID	Worker_ID	Worker_ID	Company
First Name	Employee Name	Name	Last Name
Last Name	Location	Leave Type	First Name
Status	Department	From	System_ID
Job Assignment Name	Leave Reason	To	Person Type
Department Name	TAFW Start	Pay Ending	Location
Manager Display Name	TAFW End	Days Taken	Crew
System_ID	Time requested	Roster	Gender
	TAFW days	Hours Paid	Activity
	TAFW days	Amount Paid	Forced
	Pay class	Leave Effective Rate	Date
	Pay type	Employee Std. Hourly Rate	In
		Rate Code	Logpoint In
		Ent. Lost	Date Out
		Ent. Added	Out
		Ent. Balance	Logpoint Out
		Ent. Units	Hours
		Comment	Overstayer
			Site Contact
			Legacy ID
			Period StartID
			Last Updated Ts

I've been trying so many different ways to do this but I'm lost. Any suggestions or help would be greatly appreciated. 

[Anonymous]

Hi @jodie_l ,

Try to simplify the table and provide sample data and then finally give your expected output.

How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will increase the probability that the question will be solved.

There are some similarities between HR analytics and your question, and the model also needs to consider entry date and exit date filters. Here are some examples to help you.

HR Analytics - Active Employee, Hire and Terminati... - Microsoft Fabric Community

Power BI Challenge - HR Data Analysis - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Tutu_in_YYC]

Looks like you just need to perform a few Merges in Power Query editor. Just merge all into 1 table using WorkerID, and for table 4, probably use FirstName combined with LastName.

BUT...one thing you need to be aware off, depending on the granularity of those tables, a merge may not be the right thing to do. Ideally (and simplified), 1 fact table should have 1 grain and address 1 business process..

If you have multiple grains and processes, it is fine to not merge all of them. You can still use a dimension ( example is you date table), to connect (relationship) to all the tables. This way you can still visualize all information from the different tables, based on your date selection in the date table.

If you need to filter by worker/firstname/lastname, create a dimension for workers. And relate this to all the tables.