Data Modelling Doubt

Hi Madhans 

First of all, I have a question to confirm with you. Does the Date in the sample result you gave come from the Calendar table? But in the data you gave, the date in the sample result does not exist in the Calendar table, but exists in the Inspection table.

After my test, the DOC in the Main table is passed to the Inspection table through IMO, but it still seems that it cannot determine the IMO corresponding to DOC. Could you please consider implementing this effect in Transform Data (Power Query)? If you can, the following is my test process for your reference. During the test, I put the date in the Inspection into the result table. If I understand it wrong, please feel free to correct me.

Transform data -> New Source -> Blank Query -> Advanced Editor

Then put the following into Advanced Editor.

let
    Source = Table.NestedJoin(Inspection, {"IMO"}, Main, {"IMO"}, "Main", JoinKind.FullOuter),
    #"Expanded Main" = Table.ExpandTableColumn(Source, "Main", {"Doc", "Take Date", "Hand Date"}, {"Doc", "Take Date", "Hand Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Main", "Custom", each if [Inspection Date] >= [Take Date] and [Inspection Date] <= [Hand Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Take Date", "Hand Date", "Custom"})
in
    #"Removed Columns"

Close & Apply

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi v-xuxinyi-msf,

Thanks for the effort!!!

Point 1:
kindly find my corrected Calendar table below
It contains all date from 2023 to 2024.

Point 2: