number of occurrence in a table occuring between 2 hours

[DonPepe]

Hello,

I am currently trying to calculate the number of occurrence of a string in a table occuring between 2 hours. 

I have one table with several column including these column :

Earliest	Latest	Grouped Activity
10-06-21 23:25	11-06-21 00:05	Load/unload

"grouped activity" is constuted with diferent strings (A, B, C, D,..., H)

And I want to know the number of occurence of each string delta T per delta T included between "earliest" and "latest" all day long (from 00h00 to 23h59). 

So basicaly I did it in VBA like this :

For i = 00h00 To 23h59 Step Delta T

  For j = 2 To LastRow

    If i >= TimeValue(shRaw.Cells(j, 5)) And _ ‘i >= Earliest

       i <= TimeValue(shRaw.Cells(j, 6)) Then ‘i <= Latest

         nbrOccurence = nbrOccurence + 1

         Select Case shRaw.Cells(j, 10).Value ‘ value of grouped activity

         Case "A"

         a = a + 1

         Case "B"

         b = b + 1
     'etc with C, D,...,H
         End Select

      End If

    Next j

 f = f + 1

 shPres.Cells(f, 2).Value = i

 shPres.Cells(f, 3).Value = a

 shPres.Cells(f, 3).Value = b
  'etc with C, D,...,H
Next i

Now I would like to do it in Power Bi but I don't know how. 

I don't understand the strategy to apply here. 

Thanks for the help.

[v-yalanwu-msft]

Hi, @DonPepe ;

Can you share your data and the results you want to output in the form of screenshots or files and remove sensitive information? In order to better understand your logic.

Best Regards,
Community Support Team_ Yalan Wu

[DonPepe]

Hello @v-yalanwu-msft,

Sorry for the late reply. 

So, I have a file with raw data of more than 200k line like :

With "Eariest" the start of the activity and "Latest" the end.

Here all the different activity and TC name :

Each activity "drive" is link to a "Type" (Drive 2 to A, Drive 3 to B, ...) 

So my goal is to sample for each Δt (here 45 min but i want to be able to change it) along the day, the number of each "type" in activity at this time.

So here the result I want :

I did it in VBA in Excel with the code above but I don't understant how to do it in Power Bi.

The goal is to avoid excel for report updating. 

Thanks a lot for your answer, 

Don