Forum Discussion
Linking tables based on between date
Hi everyone,
I am trying to link two tables with relationship. Table 1 should be connected using the Date and Product with table 2 where I have Product ID and between dates. So if Product ID matches and date is in between those two dates, it should create a relationship. Is there any good way to create this rather then writing DAX in table 1 since tables are quite large and might slow down the model.
Can Power Query perform faster?
Example:
-table 1
| Date | Product ID | Sales |
| 05-05-2021 | P1 | 50 |
| 06-06-2021 | P1 | 75 |
| 07-07-2021 | P1 | 10 |
-table 2
| Product ID | Date from | Date to | Attribute |
| P1 | 01-01-2021 | 31-10-2021 | A1 |
| P2 | 01-01-2021 | 31-12-2021 | A2 |
Thank you!
I have managed to solve it with dax, and it calculates it in like 10sec. Doesnt look nice, but it does the trick.
Calculated Column =
CALCULATE (
FIRSTNONBLANK ( tab2[Attribute], tab2[Attribute] ),
FILTER (
tab2,
tab1[Product ID] = tab2[Product ID]
&& tab1[Date] >= tab2[DateFrom]
&& tab2[Date] <= tab2[Date To]
)
)
13 Replies
Hi MrMP
M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVBSIjAyNDJR2lABBhaqAUqwOUMNMFImQJc1OIhLkuECFLGAJ1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Sales", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type","test",each Table.SelectRows(Table2,(tb2) => tb2[Date from]<= _[Date] and tb2[Date to] >= _[Date] and tb2[Product ID]=_[Product ID])), #"Expanded test" = Table.ExpandTableColumn(#"Added Custom", "test", {"Attribute"}, {"test.Attribute"}) in #"Expanded test"Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your time. I have tried it and it still loads for a very long time. I have no idea how to proceed. Maybe move this to SQL before it enters power query.
Hi MrMP
If you move it to SQL before ti enters power query, will it help?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
MrMP , if you only need attributes from table 2, then try a new column in table 1. It should support this on table with few million rows
New column in table 1 = maxx(filter(Table2, Table1[date] >= table2[from Date] && Table1[Date] <= Table2[To date]) , Table2[Attribute])
Thank you for help. I had to add table1.product id = table2.product id also there, but it keeps calculating for last 10mins. table 1 10mil rows, table 2 1mil rows.
