Creating Many to Many Relationship

[BrianJ]

@gkhokher ,

Here’s how I would handle this from a data modeling perspective:

1. delete both the Work Order Per Day and Last 12 Hours tables - they are almost completely duplicative of other tables already in the model, and all the info can be pulled from other existing tables
2. in your transactions table, split Posting Date Time into two separate fields - a Post Date and a Post Time field, via Power Query split by delimiter function. Make sure the resulting columns are set to type Date and type Time respectively
3. mark your Dates table as a date table (critical for any time intelligence analysis)
4. connect the Dates table to the Transactions table in a unidirectional 1:M relationship
5. add a Time table to your model, @Melissa provides the M code for an excellent one here:

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

6. connect your Time table to your Transactions table in a unidirectional 1:M relationship with Posting Time

Presto! Optimized Star Schema data model , that should support any type of time intelligence analysis you want to do.

I hope this is helpful.

• Brian