CODEWITHSUNDEEP
daxpowerbipowerquery
NicolaeGP
NicolaeGP

Reputation: 45

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

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?

Upvotes: 1

Views: 2652

Answers (3)

NicolaeGP
NicolaeGP

Reputation: 45

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?

Upvotes: 0

Carl Walsh
Carl Walsh

Reputation: 6999

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"

Upvotes: 1

Rory
Rory

Reputation: 969

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] )
)

Upvotes: 0

Related Questions