Pivot and aggregate data

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.