How to use M code create a column contain date and text

[wangjuan303]

Hi Team, I need create a Slicer column like this use M code, this column should show latest date and Previous date, other date show original format, if you have any good idea, please share to me, Thank you ahead. 

[v-yalanwu-msft · Accepted solution]

Hi, @wangjuan303 ;

Try it.

= Table.AddColumn(#"Changed Type", "Custom", each if [Date] = List.Max(#"Changed Type"[Date]) then "Latest date" 
else if [Date] = List.Sort(#"Changed Type"[Date],Order.Descending){1} then "Previous date" else [Date])

The complete M language：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQAQiUdMFPXAISUYnWgMsYGpnAZY10gBy5jaWQEl7HUBXJgMoZGRnA9hka6QE5sLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateKEY = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKEY", Int64.Type}, {"Date", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Custom", each if [Date] = List.Max(#"Changed Type"[Date]) then "Latest date" 
else if [Date] = List.Sort(#"Changed Type"[Date],Order.Descending){1} then "Previous date" else [Date])
in
    Custom1

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

HI @wangjuan303 ,

Yes you can definitely get this.

What are the rules behind getting current date, previous date in the date column?

Thanks,

Pragati

[wangjuan303]

if Date column sorting by desc, then latest date will be the first date, previous date will be second date, Date column won't show everyday, so latest date will max date, previous date is latest date closest day in Date column

[wangjuan303]

need to show the latest two date as "Latest Date" and "Previous Date", the other date show original format , Thank you

[Pragati11]

Hi @wangjuan303 ,

I will ask again as may be I was not clear earlier.

• Latest date makes sense - which should be the maximum date in your date column. Right?
• Previous date - what should be this? Just one day before latest date or something else? For example: if Latest date is 4th feb 2022, then previous date will be 3rd feb 2022. The reason for this question is - in your screenshot: latest date is 12/25/2021; but previous date is 9/22/2021 which is nearly 2 months before latest date.

Thanks,

Pragati