Add calculated custom column using entire table for calculation power query or dax

1
votes

I have this table in Power BI with events concerning some objects

|new_state |object_id | created_at |

|new |1 |11/4/2015 1:50:48 PM |

|in_use |3 |11/4/2015 2:31:10 PM |

|in_use |1 |11/4/2015 2:31:22 PM |

|deleted |2 |11/4/2015 3:14:10 PM |

.....

I am trying to add a calculated column either in DAX or power query so that for each row I would have the previous_state of that object. From a logical point of view it's not difficult: you group by id and for each row in that group you look for the closest previous time and get the "new_state" which would represent the previous state for that row. I have tried doing this by creating a function in power query and use it in a custom column but I am getting a "cyclic reference detected" error and cannot do it. Any ideas on solutions?

3
daxpowerbipowerquery
Can you copy your query here too? Are you doing something like table = Table.AddCustomColumn(... table{[Index]-1}? (Where [Index] column is the index column you added before) in that case, you could try referencing the table as "@table" inside function parameters – Oğuz Yıldız
I might have not explained the problem good enough. |new_state |object_id | created_at | |new |1 |11/4/2015 1:50:48 PM | |in_use |3 |11/4/2015 2:31:10 PM | |in_use |1 |11/4/2015 2:31:22 PM | If this was the whole table I would like it to turn into: |new_state |object_id | created_at | previous_state| |new |1 |11/4/2015 1:50:48 PM | null | |in_use |3 |11/4/2015 2:31:10 PM | null | |in_use |1 |11/4/2015 2:31:22 PM | new | I want the previous value of that certain object. – NicolaeGP
The way I was thinking is: Add an empty "previous_value" column. Group by ID; afterwards, for each row in the group (let's call it row A the current one in the loop) find the closest previous created_at time and add the "new_state" of the found row to "previous_value" at row A. I don't know if this is even possible let alone how to do it. – NicolaeGP
Or create a function in power query that gets the object ID and the created_at datetime, filters the whole table by that ID, finds the row with closest previous created_at datetime and returns the "new_value" of that row. Apply this function for each row of the table in a calculated column. This results in a cyclic reference error :( – NicolaeGP
Or as a hack: Sort them by ID, sort them by created_at, add the indexing, go trough the rows and if the previous row has the same objectID set the previous_value as the "new_value" of the previous row otherwise set it to null. – NicolaeGP

3 Answers

1
votes

It's hard to express comparisons between rows today in Power Query. Most of the functions assume the table is just an unordered set of rows.

To expand on Oğuz's comment, you could add an index column, then add a column PreviousState indexing into the previous row (or null). As an optimization it might be much faster if you buffer the whole table first.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqslLLY8vLkksSVWoyU/KSk0uic9MUahRSC5KBYqlxCeWKNQoxepAFCrUGAKRob6JvpGBoamCoZWpgZWJhUKAL0xNZl58aTHQJGNkZUZWxoZWhgZYlBliKDMyQlKWkpqTCnSDQo0RsjpjK0MThHGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.5"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{},Text.Trim),
    #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text"),
    ChangedType = Table.TransformColumnTypes(#"Promoted Headers",{{"object_id", Int64.Type}, {"created_at", type datetime}, {"new_state", type text}}),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1),
    Buffer = Table.Buffer(#"Added Index"),
    #"Added Custom" = Table.AddColumn(Buffer, "PreviousState", each try Buffer{[Index] - 1}[created_at] otherwise null),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Added Custom", "TimeDifference", each [created_at] - [PreviousState], type duration)
in
    #"Inserted Time Subtraction"
0
votes

There are surely neater solutions than this but in DAX you can create a calculated column (prevdate) to store the datetime of the previous entry:

=
CALCULATE (
    MAX ( [created_at] ),
    ALL ( table1 ),
    Table1[created_at] < EARLIER ( [created_at] ),
    Table1[object_id] = EARLIER ( [object_id] ) )

Then you add another calculated column to store the state at that previous time:

=
CALCULATE (
    VALUES ( Table1[new_state] ),
    ALL ( Table1 ),
    Table1[created_at] = EARLIER ( Table1[prevdate] ),
    Table1[object_id] = EARLIER ( Table1[object_id] )
)
0
votes

I've solved it :D 

#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"object_id", Order.Ascending}, {"created_at", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
Buffer = Table.Buffer(#"Added Index"),
#"Added Custom" = Table.AddColumn(Buffer, "PreviousState", each try (if Buffer{[Index] - 1}[object_id]=Buffer{[Index]}[object_id] then Buffer{[Index] - 1}[new_state] else null ) otherwise null)

I'm not sure it's not mostly a hack but it seems to be working. Do you see any point where it might fail in the future?