Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I would like to know how I can create a new Table by considering the LAST ENTRY for each item by each month. In this cenario, I just used a few rows from a large table in which we have basically one entry for each item every business day. Sometimes, the item is no longer used, so the last entry should be the last day (As you can see with item 2, which had its last entry on May,3rd).
Example: For the table below it should show up:
1 | 30-04-2021 | 50,165.04
2 | 29-04-2021 | 85,400.46
3 | 30/04/2021 | 93,419.76
1 | 10-05-2021 | 50,192.43
2 | 03-05-2021 | 85,677.70
3 | 10-05-2021 | 95,251.63
Table: DAILY_VALUES
Item Date Value
1 23-04-2021 50,144.18
2 15-04-2021 84,659.08
2 16-04-2021 84,852.98
2 19-04-2021 85,141.63
2 20-04-2021 85,359.74
2 23-04-2021 85,034.79
3 15-04-2021 94,407.04
3 16-04-2021 94,629.82
3 19-04-2021 94,995.20
3 20-04-2021 94,187.51
3 23-04-2021 92,685.31
2 27-04-2021 85,540.01
1 27-04-2021 50,152.52
3 27-04-2021 93,454.40
1 29-04-2021 50,160.88
2 29-04-2021 85,400.46
3 29-04-2021 93,538.86
1 30-04-2021 50,165.04
3 30-04-2021 93,419.76
1 01-05-2021 50,169.22
2 01-05-2021 85,677.70
3 01-05-2021 93,419.76
1 03-05-2021 50,169.22
2 03-05-2021 85,677.70
3 03-05-2021 93,151.08
1 04-05-2021 50,173.40
3 04-05-2021 93,661.59
1 05-05-2021 50,177.57
3 05-05-2021 93,661.59
1 06-05-2021 50,181.75
3 06-05-2021 93,862.41
1 07-05-2021 50,187.08
3 07-05-2021 93,716.70
1 10-05-2021 50,192.43
3 10-05-2021 95,251.63
Solved! Go to Solution.
Power Query solution:
Add a Month column : Date.Month([Date])
Duplicate the query (call it q2)
With q2, Do a GroupBy on Item and Month with aggregation of Max on the Date column.
Merge this table with the original (Inner Join on Item and Date columns) to bring back the value from original table.
Let me know how you get on
Power Query solution:
Add a Month column : Date.Month([Date])
Duplicate the query (call it q2)
With q2, Do a GroupBy on Item and Month with aggregation of Max on the Date column.
Merge this table with the original (Inner Join on Item and Date columns) to bring back the value from original table.
Let me know how you get on