Forum Discussion
Pivot and aggregate data
Sorry My mistake, the first date range starts with hour 8:00 till 12:00.
Do you mean that all date ranges before 100% will be calculated "average value"? And just keep the last date range?--> yes, th econdition to look for is health not equal to 100%
In addition, how to judge a date range in your orginal data structure, all datetime records in one column? --> yes they are all in a column
Hi PowerBI88 ,
Sorry I didn't make it clear. For my last question, what I want to confirm is that the health value is for a date range, but in your orginal data structure, all datetime records are in one column. Then how do you define a date range? And how do you define the health value?
Best Regards,
Icey
It is not directly defined in the data. You can infer it by iterating over the table till you meet the 100% health value. You have to look for the end date before you meet the row with the 100% health value.
Let me know if it is clear.
Hi PowerBI88 ,
Please check if this could meet your requirements:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBBCsQwCEWvUgKzK1QNdjLZ1WuU3v8a04Emism4cPXQ/33nmQgIt7zhghUgrem4h+CVrtUwqtQYe5Zrbqx4xhX5YQge7ho4LCIr/AywKPRn6e7a+7w9y/pH8az8TaQFzZNjoi076Hm0SqBVAq0SaJVI63SxNZVI6+xs1yqB1klk1zpJVK3zRFuWB2je3H/w+gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, System = _t, Health = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"System", type text}, {"Health", Percentage.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each Date.From([Date])), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"System", Order.Ascending}, {"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Date End", each let System_ = [System], Date_ = [Dates] in let t_ = Table.SelectRows(#"Added Index",each [System]=System_ and [Dates] = Date_) in if [System] = System_ and [Dates] = Date_ then if [Date]=List.Max(t_[Date]) then List.Max(t_[Date]) else if [Health]=1 then #"Added Index"[Date]{[Index]-1} else null else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date Start", each let System_ = [System], Date_ = [Dates] in let t_ = Table.SelectRows(#"Added Index",each [System]=System_ and [Dates] = Date_) in if [System] = System_ and [Dates] = Date_ then if [Date]=List.Min(t_[Date]) then List.Min(t_[Date]) else if [Health]=1 then #"Added Index"[Date]{[Index]+1} else null else null), #"Filled Up" = Table.FillUp(#"Added Custom2",{"Date End"}), #"Filled Down" = Table.FillDown(#"Filled Up",{"Date Start"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Health] <> 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"System", "Dates", "Date Start", "Date End"}, {{"Average", each List.Average([Health]), type nullable number}}), #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average", Percentage.Type}}) in #"Changed Type1"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am reproducing your steps but it seems like power bi has memory issues in performing the iterations. I have launched it 15 minutes ago 😞