Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help with creating week number / week sequence number

Hi all! Hope you guys help me with my problem about week number.   Details are: I have dataset consists of 2 types such as (request or incident), created date and time.   What I need to do is to...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    sure it's possible. Everything is possible with Power Query 🙂

    See this example

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2tTDTMTVQitUBcczMLXSMTKEcc3MDHVMEx1LH2BjKsTA00bEA6okFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangeNumberToDate = Table.TransformColumns(Source,{{"Date", each DateTime.From(Number.From(_)), type datetime}}),
    AddedCustomColumn = Table.AddColumn(ChangeNumberToDate, "Week of month", each Date.WeekOfYear
        (
            [Date]
        )
        -
        Date.WeekOfYear
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            )
        )
        +1)
in
    AddedCustomColumn

    Copy paste this code to the advanced editor to see how the solution works

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Yes. That's right. I just need the week number of the month. Is there any way to do it? 

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

sure it's possible. Everything is possible with Power Query 🙂

See this example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2tTDTMTVQitUBcczMLXSMTKEcc3MDHVMEx1LH2BjKsTA00bEA6okFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangeNumberToDate = Table.TransformColumns(Source,{{"Date", each DateTime.From(Number.From(_)), type datetime}}),
    AddedCustomColumn = Table.AddColumn(ChangeNumberToDate, "Week of month", each Date.WeekOfYear
        (
            [Date]
        )
        -
        Date.WeekOfYear
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            )
        )
        +1)
in
    AddedCustomColumn

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Jimmy!

     

    A little again, is it okay? The solution you gave, it work but there's one more problem because it just happen that it start on sunday but what I need is week of month that start on monday.

    Is there any way for that to happen? Hope you can help me. I'm sorry, I'm just a beginner.

    Thank you! 

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      hello Anonymous 

       

      you should mention this always at the beginning. However, I adapted the code to your needs

      However.. in dates in weeks that doesn't start at monday you will have week 0

       

       

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2tTDTMTVQitUBcczMLXSMTKEcc3MDHVMEx1LH2BjKsTA00bEA6okFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangeNumberToDate = Table.TransformColumns(Source,{{"Date", each DateTime.From(Number.From(_)), type datetime}}),
    AddedCustomColumn = Table.AddColumn(ChangeNumberToDate, "Week of month", each 
    let
        NormalweekNumber = 
        Date.WeekOfYear
        (
            [Date]
        )
        -
        Date.WeekOfYear
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            )
        )
        +1,
        Monday = 
        if Date.DayOfWeek
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            ),
            Day.Monday
        )= 0 then 0 else -1
    in 
        NormalweekNumber + Monday)
in
    AddedCustomColumn

       

       

       

      Copy paste this code to the advanced editor to see how the solution works

      If this post helps or solves your problem, please mark it as solution.
      Kudos are nice to - thanks
      Have fun

      Jimmy