Forum Discussion
omarelmb123
3 years agoHelper I
Find 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...
- 3 years ago
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
AlienSx
3 years agoSuper 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