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

     

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