Forum Discussion

axelk77's avatar
axelk77
Frequent Visitor
2 years ago
Solved

Find the date from day

Hi Community,   I am stugrling  with the following issue regardind transform the day to exact date. I am pulling data from a source where in the column "Date Created" has the following variance of ...
General Comment
  • axelk77's avatar
    axelk77
    2 years ago

    Finally I managed to find the solution that fits more in my case and I share it below:

    let
         today = Date.DayOfWeek(Date.From(DateTime.LocalNow())),
                weekdayNames = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},
                createdDateText = [Created_date.1],
                createdWeekdayIndex = List.PositionOf(weekdayNames, createdDateText),
                daysDifference = if createdWeekdayIndex >= 0 then
                    let
                        diff = createdWeekdayIndex - today,
                        adjustedDiff = if diff >= 0 then diff - 7 else diff
                    in
                        adjustedDiff
                    else null,
                resultDate = if daysDifference <> null then Date.AddDays(Date.From(DateTime.LocalNow()), daysDifference) else 
    if [Created_date.1] = "Today" then Date.From(DateTime.LocalNow()) else
    if [Created_date.1] = "Yesterday" then Date.AddDays(Date.From(DateTime.LocalNow()),-1) else
    [Created_date2]
            in
                resultDate
slorin's avatar
slorin
Super User
2 years ago

Hi

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslPSaxUitWJVopMLS5JLYLxghNLSuEct6JMGDMko7SoGMYJT03JS4XzQkoRbCMDfUNDfSMDI2Mw19ASwY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
Actual_Date = #date(2023,11,27),
NewDate = Table.AddColumn(Source, "Date2", each 
  if [Date]="Today" then Actual_Date else 
  if [Date]="Yesterday" then Date.AddDays(Actual_Date,-1) else 
  try Date.AddDays(Actual_Date, -Date.DayOfWeek(Actual_Date, Expression.Evaluate("Day."&[Date], #shared)))
  otherwise Date.From([Date]), type date)
in
NewDate 

 Stéphane

axelk77's avatar
axelk77
Frequent Visitor
2 years ago

Thank you slorin, but it didn't work. I am getting the error: 

Expression.Error: A cyclic reference was encountered during evaluation.

 

Thanks for your try