Forum Discussion

Ramendra's avatar
Ramendra
Icon for Helper IV rankHelper 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 query only.

I am using custom week dates which start from Wednesday to next Tuesday.


=IF([@[Extract Date]]=MAX(A:A),"Current Week",IF([@[Extract Date]]=(MAX(A:A)-7),"Last Week"))

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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Helper IV rankHelper 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
        Icon for Community Support rankCommunity 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