Forum Discussion

PowerBI88's avatar
PowerBI88
Resolver I
5 years ago

Pivot and aggregate data

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...
Need Help
Show and Tell
Tutorial Requests
Icey's avatar
Icey
Community Support
5 years ago

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's avatar
PowerBI88
Resolver I
5 years ago

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's avatar
    Icey
    Community Support
    5 years ago

    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's avatar
      PowerBI88
      Resolver I
      5 years ago

      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's avatar
        Icey
        Community Support
        5 years ago

        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