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