Find the date from day

[axelk77]

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!!

[v-xinruzhu-msft]

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 · Accepted solution]

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

[axelk77]

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.

[slorin]

What's your code?
You must replace  [Date] with [Date Created]
Stéphane

[slorin]

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]

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