Need help to calculate max date based on non-null value

[dhirendra59]

I am new to PowerBI. Thank you in advance for your help.

I have a below table where I need to find the max datetime for customer with non-null value.

For example:

Customer A - 1/11 10 AM

Customer B - 1/12 10 AM

Customer C - 1/10 4 PM

I need 2 columns in the end, customer number (A,B,..) and max datetime (for non-null value)

Date	Time		A	B	C	D	E	F
1/10/2021	10:00 AM		40	57	57	50	70	46
1/10/2021	4:00 PM			54	56	46
1/11/2021	10:00 AM		38	50		42		58
1/11/2021	4:00 PM					60	62	39
1/12/2021	10:00 AM			42		55	59

[dhirendra59]

Thank you so much @Fowmy  and @amitchandak. You guys are amazing. 

[Fowmy · Accepted solution]

@dhirendra59 

You need to UnPivot and Group to get the expected results. I combined date and time columns into one.

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY6xDoMwDER/JfKMhB1MgG4U6FapQ7eIoUOHDt36/8KnyJUQDHdJLL+75Ezz6/emip6fL47RdDVNptm0mG60VpmkFq4jR7GJ8IU5jHe7Kpu13d/w7GCaDpiCeoAKWFVYKqtlFByRk6am93ysavSY/gDte1wJaALWDI7Ek559egsbyt/WDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type time}, {"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Time"}, "Attribute", "Value"),
    #"Inserted Merged Date and Time" = Table.AddColumn(#"Unpivoted Other Columns", "Merged", each [Date] & [Time], type datetime),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Date and Time",{"Date", "Time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "DateTime"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"DateTime", "Attribute", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Value] <> " ")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Max Date Time", each List.Max([DateTime]), type datetime}})
in
    #"Grouped Rows"

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[amitchandak]

@dhirendra59 , First unpivot the data, then you can get max date for customer like

https://radacad.com/pivot-and-unpivot-with-power-bi

calculate(Max(Table[Date time]), allexcept(Table, Table[Customer]))