How to get latest value for each day where I also have a time stamp besides the date.

[wiktorkramerato]

Hi everyone,

Tried googling my issue, but every case is somehow different or I'm too bad at this.
I have a couple of servers, with their own disks and how much space they have.
A lot of Servers with many partitions and many timestamps.
I would like to have only the latest free space% value from each single day.

ServerAndDisk                              Device                                  InstanceName            HrDate+Time 24h         Free space %

ServerBerlinC:	ServerBerlin	C:	02/07/2024 06:00	88
ServerBerlinC:	ServerBerlin	C:	02/07/2024 19:00	89
ServerTokyoD:	ServerTokyo	C:	02/07/2024 10:00	71
ServerHoustonD:	ServerHouston	F:	05/07/2024 15:00	14
ServerBerlinE:	ServerBerlin	E:	01/07/2024 16:00	90
ServerBerlinC:	ServerBerlin	C:	01/07/2024 14:30	60

I want to get results like these:

ServerBerlinC:	ServerBerlin	C:	02/07/2024 19:00	89
ServerTokyoD:	ServerTokyo	C:	02/07/2024 10:00	71
ServerHoustonD:	ServerHouston	F:	05/07/2024 15:00	14
ServerBerlinE:	ServerBerlin	E:	01/07/2024 16:00	90
ServerBerlinC:	ServerBerlin	C:	01/07/2024 14:30	60

I tried grouping by, but somehow I get many duplicates.
Much appreciate any tips. Thank you.

[dufoq3 · Accepted solution]

Hi @wiktorkramerato,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstckotysnMc7ZS0kERAHLBYgZG+gbm+kYGRiYKBmZWBgZAIQsLpVgd0rUbWkK1WyJpD8nPrsx3QegG87FpNoBoNjdE0uyRX1pckp+HpB0qAuS7gQ0wRRhgCjHA0ATD8a5YHA8WMzBEaIf63dKABL8jaTexMgZpNwNqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ServerAndDisk = _t, Device = _t, InstanceName = _t, #"HrDate+Time 24h" = _t, #"Free space %" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"HrDate+Time 24h", type datetime}, {"Free space %", type number}}, "de-DE"),
    Ad_DateHelper = Table.AddColumn(ChangedType, "DateHelper", each DateTime.Date([#"HrDate+Time 24h"]), type date),
    GroupedRows = Table.Group(Ad_DateHelper, {"Device", "InstanceName", "DateHelper"}, {{"All", each Table.MaxN(_, {"HrDate+Time 24h"}, 1) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    #"Removed Columns" = Table.RemoveColumns(CombinedAll,{"DateHelper"})
in
    #"Removed Columns"

[foodd]

Hello @wiktorkramerato , and thank you for sharing your question with the Community.  Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).  You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share the file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.