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, Tha...
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.

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.