Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 TblProjected EOH TblDateMin Projected EOH Date
tbl_ActualEOHnull4/13/20204/27/2020
tbl_ActualEOHnull4/13/20204/27/2020
tbl_ActualEOHnull4/13/20204/27/2020
tbl_ActualEOHnull4/13/20204/27/2020
tbl_ActualEOHnull4/20/20204/27/2020
tbl_ActualEOHnull4/20/20204/27/2020
tbl_ActualEOHnull4/20/20204/27/2020
tbl_ActualEOHnull4/20/20204/27/2020
tbl_ActualEOHnull4/27/20204/27/2020
tbl_ActualEOHnull4/27/20204/27/2020
tbl_ActualEOHnull4/27/20204/27/2020
tbl_ActualEOHnull4/27/20204/27/2020
nulltbl_ProjectEOH4/27/20204/27/2020
nulltbl_ProjectEOH4/27/20204/27/2020
nulltbl_ProjectEOH4/27/20204/27/2020
nulltbl_ProjectEOH4/27/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
tbl_ActualEOHnull5/4/20204/27/2020
tbl_ActualEOHnull5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
tbl_ActualEOHnull5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
nulltbl_ProjectEOH5/4/20204/27/2020
tbl_ActualEOHnull5/4/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/11/20204/27/2020
nulltbl_ProjectEOH5/18/20204/27/2020
nulltbl_ProjectEOH5/18/20204/27/2020

 

I have tried to use List.Min however it returns "cannot convert date type to type list" 

 

Thanks! 

 

@Anonymous any thoughts? 

 
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Well, something like this:

Table.AddColumn( YourTableName, "YourNewColumnName", each List.Min( YourTableName[Date] ) )

Best
D

View solution in original post

v-eachen-msft
Community Support
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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


v-eachen-msft
Community Support
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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Well, something like this:

Table.AddColumn( YourTableName, "YourNewColumnName", each List.Min( YourTableName[Date] ) )

Best
D

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors