List changes to previous record in Power Query

Power Query
1

Hi,

What I’d like to do, in PQ, is to be able to produce a column that shows any changes to the previous record. Message ID column would be the identifier and I’d like to return any changes, to the previous record, to the fields Size, Type, Location ID. As records are updated during the day it would need to work out the latest record entry by the Record Updated field which is Date/Time format.

The output column would be blank if no change to previous record but would list the changes as per the second table I’ve included if poss.Changes to previous record PQ.xlsx (12.5 KB)

image
image855×530 22.9 KB

2

Hi @DavieJoe,

Copy this into a new blank Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc9LCoMwEIDhq8isFeZhbHWbUsV16SZ4/2t0DG0cQuoDwr8I+ZhJCEBQA9FaPY+3Zpw0SA1Kw1SRGxDNBSx1AE6I8Q/rvowNk425lb00z9G82qZZ1kbGBba7pMuY13hfXNKyLjJJfzvJbpG1V9n9mPUF1mdsTkzyafJjywc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Message ID" = _t, Size = _t, Type = _t, #"Location ID" = _t, #"Record Updated" = _t, #"Update Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Message ID", Int64.Type}, {"Size", Int64.Type}, {"Type", type text}, {"Location ID", type text}, {"Record Updated", type datetime}, {"Update Date", type date}}),
    Changes = Table.AddColumn(ChType, "Changes", each 
        let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in
            Text.Combine( 
                {   (if [Size] <> ChType{myIndex}[Size]? then "Size = " & Text.From( [Size] ) else null),
                    (if [Type] <> ChType{myIndex}[Type]? then "Type = " & Text.From( [Type] ) else null),
                    (if [Location ID] <> ChType{myIndex}[Location ID]? then "Type = " & Text.From( [Location ID] ) else null)
                }, ", " ), type text
            )
in
    Changes

.
It generates this result.

image
image1324×228 8.52 KB

In your production file I would suggest to add an Index column, to mimic the current ID value.
I hope this is helpful.

2 Likes
3

Awesome, will check it out in the morning. Thank you :slight_smile:

4

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Hi @Melissa,

I added in an Index column and moved over the query to my file and I’m getting an error and I don’t understand where I’ve gone wrong

image

let
Source = Excel.Workbook(File.Contents(“FILE_PATH_MASKED”), null, true),
Original_Data_Table = Source{[Item=“Original_Data”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Original_Data_Table,{{“Index”, Int64.Type}, {“ID”, Int64.Type}, {“Message ID”, Int64.Type}, {“Size”, Int64.Type}, {“Type”, type text}, {“Location ID”, type text}, {“Record Updated”, type datetime}, {“Update Date”, type date}}),
Changes = Table.AddColumn(ChType, “Changes”, each
let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in
Text.Combine(
{ (if [Size] <> ChType{myIndex}[Size]? then "Size = " & Text.From( [Size] ) else null),
(if [Type] <> ChType{myIndex}[Type]? then "Type = " & Text.From( [Type] ) else null),
(if [Location ID] <> ChType{myIndex}[Location ID]? then "Type = " & Text.From( [Location ID] ) else null)
}, ", " ), type text
)
in
Changes

I’d really like to understand where I went wrong if poss.

Thanks

David

6

Hi @DavieJoe,

You don’t have a ChType step but a #“Changed Type” step
Just rename it in the Advanced Editor window so not in the Applied Steps pane and you should be good.

1 Like
7

Ah lovely, thanks again. Awesome stuff, you really are a M Code Wizard :grinning:

8

Glad I could help David
:+1:

1 Like
9

I’ve picked up so many things that are possible from your Power Query videos, just need to go over them a bit more to embed that understanding so I can apply it. Every day is a school day :wink:

10

That’s great to hear, thanks so much. :pray:

I learn or discover something new almost every day - and I absolutelyI love that !

1 Like
11

Just wanted to clarify for my own learning if possible.

The let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in sets up a way for the new column to iterate through each row as it creates an offset to help with comparing the previous row?

What purpose the ? at the end of this have (if [Size] <> ChType{myIndex}[Size]?

Does it clarify that is it looking at the offset Index we (well you really :rofl:) have created?

Changes = Table.AddColumn(ChType, “Changes”, each
let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in
Text.Combine(
{ (if [Size] <> ChType{myIndex}[Size]? then "Size = " & Text.From( [Size] ) else null),
(if [Type] <> ChType{myIndex}[Type]? then "Type = " & Text.From( [Type] ) else null),
(if [Location ID] <> ChType{myIndex}[Location ID]? then "Type = " & Text.From( [Location ID] ) else null)
}, ", " ), type text
)

12

Hi @DavieJoe,

let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in

this protects the first row from returning an error because it will return a negative value for that row and that is not allowed as (zero based) index value.

the ? (read, question mark) in for example: ChType{myIndex}[Size]? also protects against errors, should the requested position / field not exist than instead of an error a null value is returned

I hope this is helpful

3 Likes
13

Very much, thank you :grinning:

14

Hi,

I think I may have neglected to make clear that I need the changes in the data to reflect from the Message ID. It works fine for all the entries except for the highlighted row. Message ID 113 is a unique record but it’s picking up a change type from the previous record Message ID 112. I need any changes to reflect only against the previous instance of the same Message ID, so Message 113 would be blank in this instance.

Hopefully I made sense.

Thanks
David

image
image763×265 10.4 KB

PQ Changes to Previous Entry.pbix (50.5 KB)

15

Hi @DavieJoe,

Updated the code, paste this into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc9LCoMwEIDhq8isFeZhbHWbUsV16SZ4/2t0DG0cQuoDwr8I+ZhJCEBQA9FaPY+3Zpw0SA1Kw1SRGxDNBSx1AE6I8Q/rvowNk425lb00z9G82qZZ1kbGBba7pMuY13hfXNKyLjJJfzvJbpG1V9n9mPUF1mdsTkzyafJjywc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Message ID" = _t, Size = _t, Type = _t, #"Location ID" = _t, #"Record Updated" = _t, #"Update Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Message ID", Int64.Type}, {"Size", Int64.Type}, {"Type", type text}, {"Location ID", type text}, {"Record Updated", type datetime}, {"Update Date", type date}}),
    Changes = Table.AddColumn(ChType, "Changes", each 
        let myIndex = if [ID]-2 >=0 then [ID]-2 else 0 in
            Text.Combine( 
                {   (if [Size] <> ChType{myIndex}[Size]? and [Message ID] = ChType{myIndex}[Message ID]? then "Size = " & Text.From( [Size] ) else null),
                    (if [Type] <> ChType{myIndex}[Type]? and [Message ID] = ChType{myIndex}[Message ID]? then "Type = " & Text.From( [Type] ) else null),
                    (if [Location ID] <> ChType{myIndex}[Location ID]? and [Message ID] = ChType{myIndex}[Message ID]? then "Type = " & Text.From( [Location ID] ) else null)
                }, ", " ), type text
            )
in
    Changes

.
I hope this is helpful.

2 Likes
16

It works perfectly, thank you for the quick response. I couldn’t see how to change the M Code to achieve this, but now I can.

Thanks again

David