Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
axelk77
New Member

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

1 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

View solution in original post

6 REPLIES 6
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1701236100544.png

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.

 

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

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
Super User
Super User

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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors