Forum Discussion
omarelmb123
Helper I
3 years agoFind last changed value and the date of change using Power Query
Hello Everyone,
I've been facing this problem, i wanted to retrieve the last changed value from a giving column and get also the date of change.
the input :
IDLevelDate
| 1 | L1 | 09/10/2023 |
| 1 | L2 | 22/11/2023 |
| 1 | L3 | 29/11/2023 |
| 1 | L3 | 20/12/2023 |
| 2 | L3 | 11/01/2023 |
| 2 | L4 | 12/02/2023 |
| 2 | L4 | 13/03/2023 |
| 2 | L4 | 14/04/2023 |
| 2 | L4 | 15/05/2023 |
| 3 | L1 | 16/06/2023 |
| 3 | L1 | 17/07/2023 |
| 3 | L1 | 18/01/2023 |
| 3 | L1 | 19/01/2023 |
| 3 | L1 | 20/01/2023 |
The desired output :
IDLevelDateLast changed valueLast Changed Date
| 1 | L1 | 09/10/2023 | ||
| 1 | L2 | 22/11/2023 | L1 | 22/11/2023 |
| 1 | L3 | 29/11/2023 | L2 | 29/11/2023 |
| 1 | L3 | 20/12/2023 | L2 | 29/11/2023 |
| 2 | L3 | 11/01/2023 | ||
| 2 | L4 | 12/02/2023 | L3 | 12/02/2023 |
| 2 | L4 | 13/03/2023 | L3 | 12/02/2023 |
| 2 | L4 | 14/04/2023 | L3 | 12/02/2023 |
| 2 | L4 | 15/05/2023 | L3 | 12/02/2023 |
| 3 | L1 | 16/06/2023 | ||
| 3 | L1 | 17/07/2023 | ||
| 3 | L1 | 18/01/2023 | ||
| 3 | L1 | 19/01/2023 | ||
| 3 | L1 | 20/01/2023 |
Thank you
Hello, omarelmb123
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BDoAgEAPAv3A2aXcXUf7gDwz//4ZgRCJZLz1MemjPM0hYwtGCGUIo1UJZHtcaqhCZ3JrnHydEh2v3WqZMHpsrOPdvN9A8j2D0fAXX4dZ/SQKT5xu4eb5/dg7Pvte/r5cL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Level = _t, Date = _t]), recs = Table.ToRecords(Source), count = List.Count(recs), gen = List.Generate( () => [i = 0, r = recs{0} & [Last changed value = null] & [Last Changed Date = null]], (x) => x[i] < count, (x) => [i = x[i] + 1, r = recs{i} & (if recs{i}[ID] <> x[r][ID] then [Last changed value = null] & [Last Changed Date = null] else if recs{i}[Level] <> x[r][Level] then [Last changed value = x[r][Level]] & [Last Changed Date = recs{i}[Date]] else [Last changed value = x[r][Last changed value]] & [Last Changed Date = x[r][Last Changed Date]]) ], (x) => x[r] ), z = Table.FromRecords(gen) in z
3 Replies
- AlienSx
Super User
Hello, omarelmb123
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BDoAgEAPAv3A2aXcXUf7gDwz//4ZgRCJZLz1MemjPM0hYwtGCGUIo1UJZHtcaqhCZ3JrnHydEh2v3WqZMHpsrOPdvN9A8j2D0fAXX4dZ/SQKT5xu4eb5/dg7Pvte/r5cL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Level = _t, Date = _t]), recs = Table.ToRecords(Source), count = List.Count(recs), gen = List.Generate( () => [i = 0, r = recs{0} & [Last changed value = null] & [Last Changed Date = null]], (x) => x[i] < count, (x) => [i = x[i] + 1, r = recs{i} & (if recs{i}[ID] <> x[r][ID] then [Last changed value = null] & [Last Changed Date = null] else if recs{i}[Level] <> x[r][Level] then [Last changed value = x[r][Level]] & [Last Changed Date = recs{i}[Date]] else [Last changed value = x[r][Last changed value]] & [Last Changed Date = x[r][Last Changed Date]]) ], (x) => x[r] ), z = Table.FromRecords(gen) in z - ThxAlot
Super User
- omarelmb123
Helper I
Yea and it s so fast but i want to use this created column in other fact table "No option to use relation". what would u suggest in this case ?