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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi axelk77 

You can put the following code to advanced editor

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslPSaxUitWJVopMLS5JLYLxghNLSuEct6JMGDMko7SoGMYJT03JS4XzQkqh7FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Date]="Today" then DateTime.Date(DateTime.LocalNow()) else Date.AddDays(DateTime.Date(DateTime.LocalNow()),-[Index]))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

axelk77's avatar
axelk77
Frequent Visitor
2 years ago

Thank you for replying, but assuming today is Wednesday, 29/11, you will see that the dates are wrong if you look closer at the data you provided. On Saturday, the date was 25/11 and not 27/11 etc.