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 !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!