Forum Discussion
Multiple Fact Tables Reconciliation
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.
2 Replies
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.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 CommunityThis 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 TeamIf 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
