Create a daily count out of date range

[Kaalbartje]

Hello,

I'm relative new of working in PowerBI. I always used Excel but I do not want a extra stap in my date forming to a dashboard.

The problem is: there is a database of patients in the hospital. Every movenment the patient makes is logged. When the patient is moved to an other bed it is logged and also when te patient moves to another department.

De ID number is the number for that admission and is a unique number.

What I want is a list of all the patients where they are by date.

So like this:

I hope my question is clear and maybe there is someone to help me.

Greets

Bart

[tackytechtom]

Hi @Kaalbartje ,

Here a solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/BDYAgDABXMX2b0BYQ6E/XIOy/hiKBoiHhdblrac5AbB3sQIYMI6WNULC+h/FgJM43dlYXyj7CleQ6Q2XXP1xJyoJY1NC+oT9CnL+KUSg1SVn6bOR1qJKdhvW7640OSrkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Id number" = _t, #"Start date time" = _t, #"End date time" = _t, Department = _t, Room = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id number", Int64.Type}, {"Start date time", type datetime}, {"End date time", type datetime}, {"Department", type text}, {"Room", Int64.Type}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Start date time", DateTime.Date, type date}, {"End date time", DateTime.Date, type date}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Date", "Custom", each { Number.From([Start date time])..Number.From([End date time]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Start date time", "End date time", "Custom", "Id number", "Department", "Room"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start date time", "End date time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}})
in
    #"Renamed Columns"

Let me know if you get stuck somewhere or if something is not as you expected 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Kaalbartje]

Hello,

There some active patients. They don't have an end date. When I want to do your trick PowerBI gives an error whith the null.

How to handle that error without a filter on the null. I want to see that patients too.

[tackytechtom · Accepted solution]

Hi @Kaalbartje ,

I would suggest it the same way as @vojtechsima did.

Before:

After:

I added an additional applied step where I substituted the missing date with today's date.

Here the complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoAgDAS/YjibuG1BhZt+g/D/byioVE0TTpMZSsnZEYt3o6OJJgbFgZBQz8m4M0o+XGyrritjDy3JPwzK9n9oScqWJNBQWhjmZX0/FWuieEnK4mci26FK8rrs2bvu6FsIgD47cpfEYFufeIeWpJ9YDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Id number" = _t, #"Start date time" = _t, #"End date time" = _t, Department = _t, Room = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id number", Int64.Type}, {"Start date time", type datetime}, {"End date time", type datetime}, {"Department", type text}, {"Room", Int64.Type}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Start date time", DateTime.Date, type date}, {"End date time", DateTime.Date, type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Date",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"End date time"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each { Number.From([Start date time])..Number.From([End date time]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Start date time", "End date time", "Custom", "Id number", "Department", "Room"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start date time", "End date time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}})
in
    #"Renamed Columns"

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 

[Kaalbartje]

Yes got it.

Tnx for the code and also @vojtechsima for the help.

I was thinking to difficult 🙃

I'll mark the solution

[vojtechsima]

Hi, @Kaalbartje ,
Add a new column, let's say End Date Time and write a condition that would check if it's null, if it's null put there Today's date and 23:59:59 Time, if it's not null, put there a value from the existing End Date Time column.

Then use this new column in that calculation from before.

[Kaalbartje]

Wow, that is quick.

I copied paste your code and it seems the perfect solution for me.

I'll apply it to my real dataset and see what is does. When that works I will mark your anwser as Solution.

Tnx!

[vojtechsima]

Hello, @Kaalbartje ,
I might have an idea, could you please share a copyable sample dataset please. 

thank you

[Kaalbartje]

Hello,

Like this? I don't know how to upload the .pbix

Id number	Start date time	End date time	Department	Room
1234	1-1-2019 10:00	2-1-2019 11:45	A	1
1234	2-1-2019 11:45	4-1-2019 01:45	B	1
1234	4-1-2019 01:45	4-1-2019 07:30	B	3
5678	1-1-2019 08:19	1-1-2019 09:45	B	2
5678	1-1-2019 09:45	3-1-2019 08:00	A	4

[Shishir22]

Hello @Kaalbartje,

Please refer below link for sorting a visual

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-change-sort 

Cheers,

Shishir

[Kaalbartje]

Hello,

I don't think it is a sorting problem. I know how to sort is but is doesn't give what I want.