Get Week number in Power Query Custom Column

[powerwill]

Hi experts!

This is the formula I use to get the Week number in Excel, that takes 1-7 of every month as Week 1, then 8-14 as Week 2..and so on...It resets back to Week 1 once the next month starts.

="Week "&IF((QUOTIENT(DAY([@[Createdon Date]])-1,7)+1)>4,4,(QUOTIENT(DAY([@[Createdon Date]])-1,7)+1))

I am now trying to replicate this formula to add a custom column in Power Query using the below M code I used but I get an error. Any thoughts?

Week =
"Week " & IF(
    (INT(DATEDIFF(MIN(TableName[Createdon Date]), MIN(TableName[Createdon Date]), WEEK) % 4) + 1 > 4,
    4,
    (INT(DATEDIFF(MIN(TableName[Createdon Date]), MIN(TableName[Createdon Date]), WEEK) % 4) + 1)
)

Error:

Expression.Error: We cannot convert the value #date(2022, 2, 8) to type Duration.
Details: Value=08-02-2022
Type=[Type]

 

[GraH]

You have that function on the ribbon to extract the Weeknumber.
Also in a recent thread we have shared a couple of ways to have isoweeknumber function available in PQ.
If I can find it back will share a link to that one.

 

[GraH]

I misunderstood this thread before.
So there is indeed an action on the ribbon (Week of Month) , however it's not exactly a replicate of your formula in excel.
You tried to concatenate text with number and that works fine in excel, it's forbidden in Power Query. So you need to turn the number to text first.
Both ways are in the query below.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type datetime}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each "Week " & Text.From(Date.WeekOfMonth([Dates], 0))),
    #"Calc Week of Month" = Table.AddColumn(#"Inserted Week of Month", "Week of Month calc", each
let
    Quotient = Number.IntegerDivide(Date.Day([Dates])-1,7)+1,
    WeekNr = if Quotient > 4 then 4 else Quotient
in
    "Week " & Text.From(WeekNr) )
in
    #"Calc Week of Month"