Forum Discussion

omarelmb123's avatar
omarelmb123
Helper I
3 years ago
Solved

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...
  • AlienSx's avatar
    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