Using Power BI to Expand Time Series Data

[emuller]

Hi Power BI Community,

I need some help with a time intelligence issue in Power BI. I have a large table of IDs with multiple statuses. I would like to be able to choose any date and see how many of my IDs were in each status on that date. I would also like to use visuals such as line or area charts to show the progression through the statuses over time.

I have an example data set here (I have edited my charts to more accurately represent my data) :

ID	Status	Status Change Date
1	Type A	1/1/2015
1	Type B	1/3/2015
1	Type C	1/6/2015
2	Type A	1/1/2015
2	Type B	1/5/2015
2	Type C	1/8/2015

Is there a way that a function in Power BI can “fill in the gaps” between the dates of the statuses? The same concept is seen below. I just select a date and I can see the count, etc of IDs in that status on that specific date? I can figure out how to do this myself in SQL but I would prefer to use the above data set and have a Power BI do this if possible. The reason I would prefer not to do this in SQL is my results would be many millions and I don’t want to load that into Power BI every time I refresh.

Date	ID	Status
1/1/2015	1	A
1/1/2015	2	A
1/2/2015	1	A
1/2/2015	2	A
1/3/2015	1	B
1/3/2015	2	A
1/4/2015	1	B
1/4/2015	2	A
1/5/2015	1	B
1/5/2015	2	B
1/6/2015	1	C
1/6/2015	2	B
1/7/2015	1	C
1/7/2015	2	B
1/8/2015	1	C
1/8/2015	2	C

[V-lianl-msft]

Hi @emuller ,

Based on your description, doing this in power bi will also cause performance issue. Moreover, each time the report is opened, the dataset in power bi will be refreshed, and it will refer to the query on the data source. Maybe the performance is not as good as doing what you want directly in the data source. Power bi is a visualization tool. It is recommended to use power Bi for visualization after processing the data source .

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JeffRobson · Accepted solution]

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