Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |