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