Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create Date column from Year and Month columns

I have a table with a column of Month NUmber and a column for Year. I want to make a date column for the 1st of the month. The new column I want to be formatted [Month Number]/1/[Year]  Example: the...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  this was to be done in PQ.

    For DAX use this 

    Column = DATE('Table'[Year],'Table'[Month Number],1)
Maitreya10's avatar
Maitreya10
Frequent Visitor
2 years ago

I don't want the enddate for all the records, only for the records having last(latest) month and year.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

For future requests, provide sample data as table so we can copy/paste (see note below my comment) and expected result based on sample data please.

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEyMjAyVorVAfKMUHgQjgmYYwTnxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t]),
    Ad_Date = Table.AddColumn(Source, "Date", each #date(Number.From([Year]),Number.From([Month]),1), type date),
    MaxDate = List.Max(Ad_Date[Date]),
    ReplaceMaxDate = Table.TransformColumns(Ad_Date, {{"Date", each if _ = MaxDate then Date.EndOfMonth(MaxDate) else _, type date}})
in
    ReplaceMaxDate
  • Maitreya10's avatar
    Maitreya10
    Frequent Visitor
    2 years ago

    Thanks

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      You're welcome. If my solution works, Accept is as Solution please. Thanks.