Power Query - date group

[Anonymous]

Hi All

I have a list of dates TIme that i wanted to group by batch batch 1 and batch 2. currently im doing it in excel and not in power BI but i cant find a community for power query specific so I post it here apologies.

I have two dates column

when i load this as a table later in excel the intended outcome or what the table will only show are the dates that are todays date and untill 12am and it will be tagged as batch 1. the next refresh should include the other dates that are not included on the first refresh so basically todays date and from 12 am onwards. the batch is an additional column just to help with grouping.

any ideas how i can solve this challenge?

Thanks in advance.

[Icey · Accepted solution]

Hi @Anonymous ,

If there is any misunderstanding, please let me know.

Do you mean this:

• "datesssss" column are from your data source (for exammple, SQL Server) and when your first load the data, "Date Today" column is show the current date/time;

datesssss	Date Today
2020/8/3 7:55:00 PM	2020/8/18 11:21:39 PM
2020/8/3 8:55:00 PM	2020/8/18 11:21:39 PM
...	...

• when the next day you load the data again, new data are loaded in, and the value "Date Today" column add new rows of the load time but don't change the previous value.

datesssss	Date Today
2020/8/3 7:55:00 PM	2020/8/18 11:21:39 PM   (the first load time, no change)
2020/8/3 8:55:00 PM	2020/8/18 11:21:39 PM   (no change)
...	...                                      (no change)
2020/8/4 7:55:00 PM	2020/8/19 10:58:23 PM    (the second load time)
2020/8/4 8:55:00 PM	2020/8/19 10:58:23 PM    (the second load time)
...	...                                       (the second load time)

If so, to my knowledge, it is not achieved currently.

If not, please share us more details.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @Anonymous 

to show this best in Excel I would reccomend to add a new column and make the calculation there to show if its batch1 or batch2.

Here an example

let
    Source = Table.FromColumns({List.DateTimes(#datetime(2020,8,01,0,0,0),500,#duration(0,3,0,0))},{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Batch", each if [Date]>=#datetime(Date.Year(DateTime.FixedLocalNow()), Date.Month(DateTime.FixedLocalNow()),Date.Day(DateTime.FixedLocalNow()),0,0,0) and [Date]<=#datetime(Date.Year(DateTime.FixedLocalNow()), Date.Month(DateTime.FixedLocalNow()),Date.Day(DateTime.FixedLocalNow()),12,0,0) then "Batch1" else if [Date]>#datetime(Date.Year(DateTime.FixedLocalNow()), Date.Month(DateTime.FixedLocalNow()),Date.Day(DateTime.FixedLocalNow()),12,0,0) and [Date]<=#datetime(Date.Year(DateTime.FixedLocalNow()), Date.Month(DateTime.FixedLocalNow()),Date.Day(DateTime.FixedLocalNow()),23,59,59) then "Batch2" else "no batch")
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

the function table.partition seems better suited to meet this grouping need

let
    Source = Table.FromColumns({List.DateTimes(#datetime(2020,8,18,6,0,0),30,#duration(0,0,20,0))},{"d"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"d", type datetime}}),
    ampm=Table.Partition(#"Changed Type","d",2, (am)=>if am<= #datetime(2020,8,18,12,0,0)then 0 else 1)
in 
    ampm

[edhans]

Not sure exactly what you are looking for. This formula will return the number of days from Aug 17, 2020 vs the date in the date column.

Duration.Days(DateTime.Date([Date]) - #date(2020,8,17))

So Aug 18 would be batch 1, Aug 19 batch 2, Aug 20, batch 3.

If that is not what you are looking for, can you post some sample data (not an image) and expected results?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum