Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
The goal is to categories table 1 with the the dates of table 2 by whichever date is closer in table 2. How can this be achieved in power query? I have provided the Dax script below.
Table 1: model with version
Model Version Number
EI1 | 17/01/2023 |
EI1 | 19/01/2023 |
EI1 | 28/01/2023 |
EI1 | 02/02/2023 |
EI2 | 05/02/2023 |
EI2 | 26/02/2023 |
EI2 | 04/03/2023 |
EI2 | 08/03/2023 |
Table 2: Dates
Date
13/01/23 |
27/01/23 |
10/02/23 |
24/02/23 |
07/03/23 |
21/03/23 |
05/04/23 |
For example
Table 3: model with version categorized
EI1 | 17/01/2023 | 27/01/23 | |
EI1 | 19/01/2023 | 27/01/23 | |
EI1 | 28/01/2023 | 10/02/23 | |
EI1 | 02/02/2023 | 10/02/23 | |
EI2 | 05/02/2023 | 10/02/23 | |
EI2 | 26/02/2023 | 07/03/23 | |
EI2 | 04/03/2023 | 07/03/23 |
etc...
Below is the Code that works how can I write it in power query?
DateAg = CALCULATE ( MIN ( 'Date'[DateList]), FILTER ( 'Date', 'Date'[DateList] >= 'EI'[Version] ) )
Solved! Go to Solution.
Hi @JL0101 ,
A possible solution:
Make sure both Version and DateList columns are set to type date.
Perfect, thank you!
Hi @JL0101 ,
A possible solution:
Make sure both Version and DateList columns are set to type date.