Forum Discussion

M001's avatar
M001
Helper I
2 years ago
Solved

Turn column names in M Language defined by variables

Hi there,    I would like to check the data in my latest month column against previous month, and return Y for rows with data in the last two columns not equal   The tricky part is the number of ...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi M001 

     

    if List.IsDistinct(List.LastN(Record.ToList(_),2)) then "N" else "Y"

    Stéphane 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi M001, check this:

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMgNgZiEyB2dFSK1YlWcoJKwaRB2NUVLOWMJAyjnZzAUi5IhhlD2c7OYClXJDtg2MVFKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"1/3/2024" = _t, #"1/4/2024" = _t, #"1/5/2024" = _t, #"1/6/2024" = _t, Column2 = _t]),
    Ad_RecentUpdate = Table.AddColumn(Source, "RecentUpdate", each 
        [ a = List.LastN(List.Sort(List.Select(Table.ColumnNames(Source), (x)=> (try Date.From(x) otherwise null) is date)), 2),
          b = if Record.Field(_, a{0}) <> Record.Field(_, a{1}) then "Y" else "N"
        ][b], type text)
in
    Ad_RecentUpdate