HELP | Joining Two Tables

[NeroTolentino]

Hello,

I'm trying to join these two tables (Table 1 and Table 2). I've tried joining (full outer) and also tried the EARLIER function but I can't get the desired result (Result Table). Can you shed some ideas on how to go around this? Thanks!

Table 1

Date	Product	Category	Count
12/1/2020	XQN	WA	72
12/5/2020	XQN	WA	83
12/4/2020	YFL	AEN	25
12/8/2020	YFL	AEN	31
12/3/2020	XQN	BAR	4

Table 2

Calendar

12/1/2020

12/2/2020

12/3/2020

12/4/2020

12/5/2020

12/6/2020

12/7/2020

12/8/2020

12/9/2020

12/10/2020

Result:

Date	Product	Category	Count
12/1/2020	XQN	WA	72
12/2/2020	XQN	WA	72
12/3/2020	XQN	WA	72
12/4/2020	XQN	WA	72
12/5/2020	XQN	WA	83
12/6/2020	XQN	WA	83
12/7/2020	XQN	WA	83
12/8/2020	XQN	WA	83
12/9/2020	XQN	WA	83
12/10/2020	XQN	WA	83
12/4/2020	YFL	AEN	25
12/5/2020	YFL	AEN	25
12/6/2020	YFL	AEN	25
12/7/2020	YFL	AEN	25
12/8/2020	YFL	AEN	31
12/9/2020	YFL	AEN	31
12/10/2020	YFL	AEN	31
12/3/2020	XQN	BAR	4
12/4/2020	XQN	BAR	4
12/5/2020	XQN	BAR	4
12/6/2020	XQN	BAR	4
12/7/2020	XQN	BAR	4
12/8/2020	XQN	BAR	4
12/9/2020	XQN	BAR	4
12/10/2020	XQN	BAR	4

[v-henryk-mstf · Accepted solution]

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.

[amitchandak]

@NeroTolentino , Create a new column in table 1.

Date 2 = minx(filter(Table1, [Date] >earlier([date] )), [Date])

Create a new table, Make sure column name are not same  , if need use selectcolumns to rename columns

new table = filter(crossjoin(Table1,table2), table2[Calendar] >= Table1[Date] && table2[Calendar] < Table1[Date 2])

also refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/