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 provide me a hint on how to achieve it?

 

Tahnk you,

Kind regards

Marta

Need Help
Show and Tell
Tutorial Requests

11 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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.

  • 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