Forum Discussion

wangjuan303's avatar
wangjuan303
Helper III
4 years ago
Solved

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

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. 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

5 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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's avatar
    Pragati11
    Super User
    4 years ago

    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's avatar
      wangjuan303
      Helper III
      4 years ago

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

      • Pragati11's avatar
        Pragati11
        Super User
        4 years ago

        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

    • wangjuan303's avatar
      wangjuan303
      Helper III
      4 years ago

      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