Forum Discussion

omarelmb123's avatar
omarelmb123
Icon for Helper I rankHelper 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

1L109/10/2023
1L222/11/2023
1L329/11/2023
1L320/12/2023
2L311/01/2023
2L412/02/2023
2L413/03/2023
2L414/04/2023
2L415/05/2023
3L116/06/2023
3L117/07/2023
3L118/01/2023
3L119/01/2023
3L120/01/2023

The desired output : 

IDLevelDateLast changed valueLast Changed Date

1L109/10/2023  
1L222/11/2023L122/11/2023
1L329/11/2023L229/11/2023
1L320/12/2023L229/11/2023
2L311/01/2023  
2L412/02/2023L312/02/2023
2L413/03/2023L312/02/2023
2L414/04/2023L312/02/2023
2L415/05/2023L312/02/2023
3L116/06/2023  
3L117/07/2023  
3L118/01/2023  
3L119/01/2023  
3L120/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

  • 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
  • CHG.pbix

    CHG.xlsx

     

    PQ is usually used for data cleansing whereas DAX is for calculations.

     

    A showcase of powerful Excel worksheet formulas,

    • omarelmb123's avatar
      omarelmb123
      Icon for Helper I rankHelper 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 ?