Forum Discussion
3 data tables - circular relationship
i think your transactional data probably needs adjusting, so work orders, merged tables and outages, how do they all relate to each other? it might be better to denormalise this data, ie flattten as much as you can into one or 2 transactional tables, its about understanding how these processes fit together. So does an outage have more than one work order? in power query it might make sense to on these relationships to merge the tables together. that way you wont have to work backwards because its in the same table. Its about understanding if the granularity of the table changes and if it makes sense to do that. Not sure if this makes sense. You can always create a few rows of sample data in mockaroo an online free way to make sample data if that is easier.
Community Championits hard to give advice with limited information, providing sample data is usually the best way to get your answer seen to.
however from what you have shared it makes more sense to me to create 3 new tables
a cost centre table with a unique list of cost centres
a outages code table with a unique list of outages code
crew numbers table with a unique list of crew numbers
then use these intermediate tables to link the tables together via these tables.
so workorder and outages will link to outage codes
work order and merged will link to crew numbers
cost cent4res will link to outages and merged
these will be one to many relationships from the intermediate tables
the bridge tables must have unique values
however not sure if this will resolved all the issues, the way you have modelled your data needs to adjust in order to meet your requirements
providing sample data is the easiest way to figure it out.
The relationships are working great, thank you so much.
My issue now is that i'm having to work backwards via the crews to get to the cost center, and the work order data is not flowing through correctly. Example for one specific outage code:
Cost center 1622 has 2 crews associated with it - crew 1 has 108 work orders and crew 2 has 2 work orders ( this is strictly from the work order table). So the total for that cost center (in one specific outage) should be 110 work orders. But in the decomposition tree, it's carrying over the total number of work order for the entire outage code, something in the thousands, because of the outage code connection between the outages table and the work order table. How would I be able to combine all of the totals for each crew into one total for each cost center?
P.S sorry for the vague details, this is a report for my company and i am worried about confidentiality.
- vanessafvg
i think your transactional data probably needs adjusting, so work orders, merged tables and outages, how do they all relate to each other? it might be better to denormalise this data, ie flattten as much as you can into one or 2 transactional tables, its about understanding how these processes fit together. So does an outage have more than one work order? in power query it might make sense to on these relationships to merge the tables together. that way you wont have to work backwards because its in the same table. Its about understanding if the granularity of the table changes and if it makes sense to do that. Not sure if this makes sense. You can always create a few rows of sample data in mockaroo an online free way to make sample data if that is easier.
