GROUP THE VALUE IN SEPARATE COLUMN

[Girish_123]

TIME

11:03:01

11:03:02

11:03:03

11:05:05

11:05:06

11:56:04

11:56:05

11:59:59

12:00:00

I have a one column contain time i want a output like IN POWER QUERY OR DAX CALCULATION

TIME	differ	Group
11:03:01		G1
11:03:02	1	G1
11:03:03	1	G1
11:05:05	122	G2
11:05:06	1	G2
11:56:04	3,058	G3
11:56:05	1	G3
11:59:59	234	G4
12:00:00	1	G4

[Girish_123]

Hi Prateek Sir i want output like this can you please help me out 

Time	GAP	Output
11:09:04		G1
11:09:05	1	G1
11:10:06	61	G2
11:10:07	1	G2
11:10:08	1	G2
11:12:01	113	G3
11:12:02	1	G3
11:43:23	1881	G4
11:43:24	1	G4
12:59:58	4,594	G5
12:59:59	1	G5
13:00:00	1	G5
13:00:01	1	G5

[Prateek97 · Accepted solution]

Hi @Girish_123 ,

I was able to replicate the scenario using Power Query. 

Added a column to extract Hours and Minutes in a column using Column From Examples or you can use below formula.

= Table.AddColumn(#"Changed Type", "Replaced Text", each Text.Replace(Text.From([TIME], "en-IN"), ":", ""), type text)

Add this column as a new query.

Convert this query to Table.

Then Remove the duplicates and add a Index Column starting from 1.

Then come back to the original data table and merge the new query.

Extract the index column from the merged table.

Then you can simply concatenate G at the beginning of the index.

Hope this works for you.

Please mark this as a solution if it works.

[Girish_123]

hI PRATEEK 11:59:59 and 12:00:00 there is 1 sec gap it should come in G4 only not in G5

[Prateek97]

Hi @Girish_123 ,

What I could understand from the original question was you wanted to group the time which was in the same hour and same minute.

If that is not the case, can you please explain what would be the logic of grouping.