Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
@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/
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |