Forum Discussion
Min Date in M with Nulls
Hello!
I am struggling to create Column [Min Projected EOH Date] below as a custom Column in M in the same table without changing the rest of the table.
Text Text Date
| Actual EOH Tbl | Projected EOH Tbl | Date | Min Projected EOH Date |
| tbl_ActualEOH | null | 4/13/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/13/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/13/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/13/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/20/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/20/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/20/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/20/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/27/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/27/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/27/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 4/27/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 4/27/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 4/27/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 4/27/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 4/27/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 5/4/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/4/2020 | 4/27/2020 |
| tbl_ActualEOH | null | 5/4/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/11/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/18/2020 | 4/27/2020 |
| null | tbl_ProjectEOH | 5/18/2020 | 4/27/2020 |
I have tried to use List.Min however it returns "cannot convert date type to type list"
Thanks!
Anonymous any thoughts?
Hi Anonymous ,
I think you add a custom column with "List.Min([Date])", but this function needs a list parameter, you should use "List.Min(#"Changed Type2"[Date])".
Use your previous step name to replace the bold part.
3 Replies
- v-eachen-msft
Community Support
Hi Anonymous ,
I think you add a custom column with "List.Min([Date])", but this function needs a list parameter, you should use "List.Min(#"Changed Type2"[Date])".
Use your previous step name to replace the bold part.
- mahoneypat
Microsoft Employee
To get the min date where ProjectEOH is present you can use this expression in your custom column
List.Min(Table.SelectRows(#"Changed Type", each ([ProjectName] = "ProjectEOH"))[Date])) where #"Changed Type" is the name of your previous step. Here is a full query example with similar data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHSUTLUN9I3MjAyUIrVQRU2RgiD+YZofBMc/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectName = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectName", type text}, {"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "MinValue", each List.Min(Table.SelectRows(#"Changed Type", each ([ProjectName] = "Project"))[Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"MinValue", type date}})
in
#"Changed Type1"If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
