Forum Discussion

Ramendra's avatar
Ramendra
Helper IV
5 years ago
Solved

Power query expression for below excel formula

Can someone please help with power query expression for below excel formula. Where Extract date is the date column and A:A is the same date column in excel. I need correct expression for Power quer...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  Ramendra 

    Use  Date.AddDays([Extract Date],-3)  to replace [Extract Date].

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xEYAgEADBXoidgT8EoRaG/tvATC/cbNdKFCKPHKR9farSLTWpS480pPkXRQpJF3RBF3RBF3RBF97LPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extract Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extract Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.IsInCurrentWeek(Date.AddDays([Extract Date],-3)) then "This Week" else if Date.IsInPreviousWeek(Date.AddDays([Extract Date],-3)) then "Last Week" else "Other week")
in
    #"Added Custom"

    Best Regards,
    Community Support Team _ Eason

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Ramendra , In power query you have function

Date.IsInCurrentWeek ,Date.IsInPreviousWeek

 

if Date.IsInCurrentWeek([Date]) then "This Week" else

if Date.IsInPreviousWeek([Date] then "Last Week" else "Other week"

end

 

 

refer: Power BI Tutorial Series for Beginners: Date.IsInCurrentWeek, Date.IsInNextWeek, Date.IsInPreviousWeek: https://www.youtube.com/watch?v=HgrG9I51Nbk 

Ramendra's avatar
Ramendra
Helper IV
5 years ago

Hi amitchandak ,

 

Sorry but I am using custom week which start from Wednesday to next Tuesday

But this formula will take standard week only.
Please correct me if I am wrong?

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi,  Ramendra 

    Use  Date.AddDays([Extract Date],-3)  to replace [Extract Date].

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xEYAgEADBXoidgT8EoRaG/tvATC/cbNdKFCKPHKR9farSLTWpS480pPkXRQpJF3RBF3RBF3RBF97LPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extract Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extract Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.IsInCurrentWeek(Date.AddDays([Extract Date],-3)) then "This Week" else if Date.IsInPreviousWeek(Date.AddDays([Extract Date],-3)) then "Last Week" else "Other week")
in
    #"Added Custom"

    Best Regards,
    Community Support Team _ Eason