Forum Discussion

Krishna_R's avatar
Krishna_R
New Member
3 years ago

Date Bins

Hi All,

 

I'm looking to create a simple table which will give me modified records in the following format of bins. But, I'm afraid is there a way to create such date bins. If yes, could anyone share the relevant article/approach to sort this.

 

Last ModifiedCount
>30 days 5
30-152
7 - 23
Yesterday1
Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Krishna_R ,

     

    In Power Query you would add a conditional column based on the column (presumably a date) that defines the bins, then group on this new column.

    Example:

    if [Date] = Date.AddDays(Date.From(DateTime.LocalNow()), - 1) then "Yesterday"
else if [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), - 7) then "7 - 2"
else if [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), - 30) then "30 - 8"
... etc.

    Then Group By this new column and add a Count aggregated column.

     

    Pete