Using Power BI to Expand Time Series Data

There are probably better ways of doing this, but one way is to create a custom function that accepts a start and end date (status 1 and 3 dates, assuming status 3 is always the latest), then generates a list of all the dates in between using a Power Query List.

You can then invoke this against your data set, unpivot the status columns, add a conditional column that puts the status name in the column when the dates match or null if not, fill down on that column, then remove the duplicates on the ID & Date columns.

Here's the PQ code (I used Enter Data to setup the table of sample data).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjA0BTONEUwzCDNWJ1rJCF2ZKYJpAVUWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Status 1" = _t, #"Status 2" = _t, #"Status 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status 1", type date}, {"Status 2", type date}, {"Status 3", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnListAllDates", each fnListAllDates([Status 1], [Status 3])),
    #"Expanded fnListAllDates" = Table.ExpandListColumn(#"Invoked Custom Function", "fnListAllDates"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded fnListAllDates",{{"fnListAllDates", "Date"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"ID", "Date"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Status", each if [Value] = [Date] then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Status"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Down", {"ID", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Date", type date}, {"Status", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"ID", "Date", "Status"})
in
    #"Removed Other Columns"

The ListAllDates custom function code is below & I converted this into a function called fnListAllDates:

let
    Source = List.Dates(StartDate, Duration.Days(EndDate - StartDate), #duration(1,0,0,0))
in
    Source

As you can see, it's using the parameters start & end date.

PBIX sample file

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Jeff Robson

www.accessanalytic.com.au