Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to create 7-day bins

I need help with the following task.

We have for example a table with orders and date. I need to calculate how many orders were created every week and display that on the chart. The 7-day bins should be created in the following way.

 

For example, we chose October 2022.
1st bin: 27.09.22 - 03.10.22
2nd bin: 04.10.22 - 10.10.22 
3rd bin: 11.10.22 - 17.10.22
4th bin: 18.10.22 - 24.10.22
5th bin: 25.10.22 - 31.10.22

Could you please suggest how this can be implemented.
Can you please suggest in Power Query Editor
Example:

Please find the below screenshot:

 

Need Help
  • sevenhills's avatar
    sevenhills
    2 years ago

    Try this PQ: Modify the start and end date to your needs i.e., date(2022,9,27) and date(2023,1,4)

     

    let
    Source = List.Generate(()=>[x=#date(2022,9,27)], each [x]<=#date(2023, 1, 4), each [x=Date.AddWeeks([x],1)], each [x]),
 

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Start Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "End Date", each Date.AddDays([Start Date], 6), type date),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

     Output:

    Hope it helps!

     

     

1 Reply

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    Try this PQ: Modify the start and end date to your needs i.e., date(2022,9,27) and date(2023,1,4)

     

    let
    Source = List.Generate(()=>[x=#date(2022,9,27)], each [x]<=#date(2023, 1, 4), each [x=Date.AddWeeks([x],1)], each [x]),
 

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Start Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "End Date", each Date.AddDays([Start Date], 6), type date),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

     Output:

    Hope it helps!