Pivot and aggregate data

[PowerBI88]

Hi,

I have got the following dataset: Date, System, Health Values:

I need to transform it in a dataset with the following columns: Day, Date start, Date end, Aggregated Health.

Can you provide me a hint on how to achieve it?

Tahnk you,

Kind regards

Marta

[Icey]

Hi @PowerBI88 ,

Please check if this is what you want:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAgDEXRrZSMBfOxHzJrtyHZ/zaqYNA4eKPD49YKjExZMh2ohJDgbSOwtAipuHAU1uIim8g8lZXYQ1/bGWWEulxRRqjLvYnM0wNmPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, System = _t, #"Health Values" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"System", type text}, {"Health Values", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Day", "System"}, {{"Date start", each List.Min([Date]), type nullable datetime}, {"Date end", each List.Max([Date]), type nullable datetime}, {"Health", each List.Sum([Health Values]), type nullable number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Day", "Date start", "Date end", "System", "Health"})
in
    #"Reordered Columns"

Best Regards,

Icey

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

[PowerBI88]

Unfortunately it is not exaclty what I am looking for. I try to explain myself better: as regards the grouping of the the dates it's not ok to calculate the min date and the max date in 1 day, because inside one day I am looking for grouping all occurence of date range where dhealth is not equal to 100%. eg: 

if I have

1/1/2021 8:00 - 1/1/2021 9:30 Health = 80%

1/1/2021 11:30  - 1/1/2021 12:00 health = 90%

1/1/2021  12:00 - 1/1/2021 13:00 health = 100%

1/1/2021 13:00 - 1/1(2021 13:30 health = 70%

In my desidered table I will need two grouping inside day 1/1/2021, respectively:

1/1/2021 11:30 - 1/1/2021 12:00 healt = average(80, 90)

1/1/2021 1/1/2021 13:00 - 1/1(2021 13:30 health = 70%

Let me know if you need further clarifications

Thank you

[Icey]

Hi @PowerBI88 ,

I am a bit unclear about the calculation logic here:

Do you mean that all date ranges before 100% will be calculated "average value"? And just keep the last date range?

In addition, how to judge a date range in your orginal data structure, all datetime records in one column?

Best Regards,

Icey

[PowerBI88]

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

[Icey]

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

[PowerBI88]

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.

[Icey]

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.

[PowerBI88]

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 😞

[PowerBI88]

Any update?

[PowerBI88]

@Fowmy Here is the link to the desired output: Link 

[Fowmy]

@PowerBI88 

Can you provide usable data in Excel or CSV with the expected results?
You attach the link to the files after saving them in OneDrive or any other location.