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 values(assuming the data are refreshed on Monday 27/11/2023): Today, yesterday ,Saturday, Friday, Thursday, Wednesdey, Tuesday, 20/11/2023, 19/11/2023, etc...

I want to convert all the values to dates. So the results will be : 27/11/2023, 26/11/2023, 25/11/2023, 24/11/2023, 23/11/2023, 22/11/2023, 21/11/2023, 20/11/2023, 19/11/2023, etc..

 

All the texts is referred to the date that the source date are refreshed. Meaning that if the data will be refreshed on Wednesday 29/11/2023 the values will be : today, yesterday, Monday, Sunday, Saturday... and it should be converted to  29/11/2023, 28/11/2023, 27/11/2023, 26/11/2023 etc..

Does anyone knows how this can be achieved in a power query level?

 

Thanks!!

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

6 Replies

  • 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

  • slorin's avatar
    slorin
    Super User
    2 years ago
    What's your code?
    You must replace  [Date] with [Date Created]
    Stéphane
  • 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.

    • axelk77's avatar
      axelk77
      Frequent Visitor
      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