HELP | Joining Two Tables

Hi NeroTolentino ,

According to the data you provided, I did the following test in power query:

let
    Source = #"Table 1",
    #"Added Custom2" = Table.AddColumn(Source, "Custom", each let 
maxdate=Table.Max(
    Table.SelectRows(Source,(x)=>x[Product]=[Product] and x[Category]=[Category]),"Date"
)[Date],
nextdate=try
Table.Min(
    Table.SelectRows(Source,(x)=>x[Product]=[Product] and x[Category]=[Category] and x[Date]>[Date]),"Date"
)[Date]
otherwise
null
in 
if [Date]=maxdate 
then 
List.Generate(
    ()=>[Date],
    each _<=List.Max(#"Table 2"[Calendar]),
    each Date.AddDays(_,1)
)
else 
List.Generate(
    ()=>[Date],
    each _<nextdate,
    each Date.AddDays(_,1)
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Product", "Category", "Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Date"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then add the corresponding column to the table in the desktop:

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

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