Building a History Table from a Change Log and Current State Table

[OptiBill]

I am attempting to build a "History" table for a Power BI report from a "Change Log" table and a "Current State" table. As an example, the "Change Log" table has the following data:

Column1	Created	Field	FromString	ToString
ABCD	10/1/2022 12:00	Assignee	User1	User2
ABCD	10/1/2022 16:00	Status	Open	In Progress
ABCD	10/2/2022 13:00	Assignee	User2	User3
ABCD	10/3/2022 6:00	Priority	2 - High	1 - Blocking
ABCD	10/9/2022 14:00	Status	In Progress	Closed

The "Current State" table has the record as it currently exists:

CreatedDatetime	UpdatedDatetime	Assignee	Key	Status	Priority
10/1/2022 10:00	10/9/2022 14:00	User3	ABCD	Closed	1 - Blocking

And my desired "History" table would be:

CreatedDatetime	UpdatedDatetime	Assignee	Key	Status	Priority
10/1/2022 10:00	10/1/2022 10:00	User1	ABCD	Open	2 - High
10/1/2022 10:00	10/1/2022 12:00	User2	ABCD	Open	2 - High
10/1/2022 10:00	10/1/2022 16:00	User2	ABCD	In Progress	2 - High
10/1/2022 10:00	10/2/2022 13:00	User3	ABCD	In Progress	2 - High
10/1/2022 10:00	10/3/2022 6:00	User3	ABCD	In Progress	1 - Blocking
10/1/2022 10:00	10/9/2022 14:00	User3	ABCD	Closed	1 - Blocking

Is this achievable in Power Query? 

[ImkeF · Accepted solution]

Hi @OptiBill ,
please check the file attached.
It is not pretty any more, but I didn't want to create a completely new logic for it.

[GARAVENA]

Is this achievable with dax? Im trying to do the same, but working with a DB in direct query.

[ImkeF]

Hi @OptiBill ,
yes, that's perfectly doable in PQ. This is the logic:

let
    Source = Excel.CurrentWorkbook(){[Name = "ChangeLog"]}[Content], 
    #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
            {"Column1", type text}, 
            {"Created", type datetime}, 
            {"Field", type text}, 
            {"FromString", type text}, 
            {"ToString", type text}
        }
    ), 
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Field"}), 
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates", {"ToString", "Created"}), 
    #"Pivoted Column1" = Table.Pivot(
        #"Removed Columns", 
        List.Distinct(#"Removed Columns"[Field]), 
        "Field", 
        "FromString"
    ), 
    #"Merged Queries" = Table.NestedJoin(
        #"Pivoted Column1", 
        {"Column1"}, 
        CurrentState, 
        {"Key"}, 
        "CurrentState", 
        JoinKind.LeftOuter
    ), 
    _Initial = Table.ExpandTableColumn(#"Merged Queries", "CurrentState", {"CreatedDatetime"}, {"Created"}), 
    #"Pivoted Column" = #"Changed Type", 
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column", {"FromString"}), 
    _Ongoing = Table.Pivot(
        #"Removed Columns1", 
        List.Distinct(#"Removed Columns1"[Field]), 
        "Field", 
        "ToString"
    ), 
    Custom1 = _Initial & _Ongoing, 
    #"Filled Down" = Table.FillDown(Custom1, {"Assignee", "Status", "Priority"})
in
    #"Filled Down"

Please check out the file enclosed as well.

[OptiBill]

Thanks @ImkeF! This looks amazing and it directly answers my initial question. However, I didn't realize that my example wasn't inclusive of all scenarios, which was my fault. When including additional data which may have some, all, or none of the same changes it looks like the current solution is breaking down.

For example, if an additional "Change Log" entry is added for Key EFGH so that the "Change Log" now reads as 

Column1	Created	Field	FromString	ToString
ABCD	10/1/2022 12:00	Assignee	User1	User2
ABCD	10/1/2022 16:00	Status	Open	In Progress
ABCD	10/2/2022 13:00	Assignee	User2	User3
ABCD	10/3/2022 6:00	Priority	2 - High	1 - Blocking
ABCD	10/9/2022 14:00	Status	In Progress	Closed
EFGH	10/3/2022 14:00	Assignee		User1

And a corresponding additional "Current State" entry is added for Key EFGH, as well as an entry for Key IJKL (which has no entries in the "Change Log" since no changes have been made since it's initial creation) so that the "Current State" reads as

CreatedDatetime	UpdatedDatetime	Assignee	Key	Status	Priority
10/1/2022 10:00	10/9/2022 14:00	User3	ABCD	Closed	1 - Blocking
10/2/2022 11:00	10/3/2022 14:00	User1	EFGH	Open	1 - Blocking
10/1/2022 8:00	10/1/2022 8:00		IJKL	Open	2 - High

The resulting "History" table is showing as

Column1AssigneeStatusPriorityCreated

ABCD	User1	Open	2 - High	10/1/2022 10:00:00 AM
ABCD	User2	Open	2 - High	10/1/2022 12:00:00 PM
ABCD	User2	In Progress	2 - High	10/1/2022 4:00:00 PM
ABCD	User3	In Progress	2 - High	10/2/2022 1:00:00 PM
ABCD	User3	In Progress	1 - Blocking	10/3/2022 6:00:00 AM
ABCD	User3	Closed	1 - Blocking	10/9/2022 2:00:00 PM
EFGH	User1	Closed	1 - Blocking	10/3/2022 2:00:00 PM

The desired "History" table would instead be

Column1	Assignee	Status	Priority	Created
ABCD	User1	Open	2 - High	10/1/2022 10:00:00 AM
ABCD	User2	Open	2 - High	10/1/2022 12:00:00 PM
ABCD	User2	In Progress	2 - High	10/1/2022 4:00:00 PM
ABCD	User3	In Progress	2 - High	10/2/2022 1:00:00 PM
ABCD	User3	In Progress	1 - Blocking	10/3/2022 6:00:00 AM
ABCD	User3	Closed	1 - Blocking	10/9/2022 2:00:00 PM
EFGH		Open	1 - Blocking	10/2/2022 11:00:00 AM
EFGH	User1	Open	1 - Blocking	10/3/2022 2:00:00 PM
IJKL		Open	2 - High	10/1/2022 8:00:00 AM

It looks like the EFGH Key is missing a row AND has the wrong Status for the row that is included. Additionally, the row for IJKL is missing altogether.

Can your solution be enhanced to handle these additional uniquenesses?