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

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Anonymous ,

 

don't know if I got you right...

You don't need the weeknumber of the year, but the weeknumber of the month? Is this right?

 

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
      Icon for Community Champion rankCommunity 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! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Its just that I need a solution like this