cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhirendra59
Helper I
Helper I

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

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)

 

DateTime ABCDEF
1/10/202110:00 AM 405757507046
1/10/20214:00 PM  545646  
1/11/202110:00 AM 3850 42 58
1/11/20214:00 PM    606239
1/12/202110:00 AM  42 5559 
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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"

 

Fowmy_0-1610608169153.png

________________________

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

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
dhirendra59
Helper I
Helper I

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

Fowmy
Super User
Super User

@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"

 

Fowmy_0-1610608169153.png

________________________

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

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

amitchandak
Super User
Super User

@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]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!