Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emuller
Frequent Visitor

Using Power BI to Expand Time Series Data

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

IDStatusStatus Change Date
1Type A1/1/2015
1Type B1/3/2015
1Type C1/6/2015
2Type A1/1/2015
2Type B1/5/2015
2Type C1/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.

 

DateIDStatus
1/1/20151A
1/1/20152A
1/2/20151A
1/2/20152A
1/3/20151B
1/3/20152A
1/4/20151B
1/4/20152A
1/5/20151B
1/5/20152B
1/6/20151C
1/6/20152B
1/7/20151C
1/7/20152B
1/8/20151C
1/8/20152C
1 ACCEPTED SOLUTION
JeffRobson
Frequent Visitor

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 

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

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
Frequent Visitor

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.