Splitting hours in 5 minute incrementals and add to existing table

[lejo54]

Hi.

I'm fairly new to this and strugglig a bit with a solution. In the example below i have a large table containing months in coulmn A, Wekk# in column B, date in C, time of each occurence in D and number of occurences in E. I need to add a time column that splits each hour into 5 minute increments from 00:00,00;05,00:10 up to 23:55. The expected result is a new column that will display 0 count if no occurence from source data (column E) and the exact count from column E when hour increment and time match. Does this make sense..? Thanks

[AlienSx · Accepted solution]

Hello, @lejo54 do you need a count of occurences or a value in ColumnE? PLease show the result you want to get. Here I added all missing 5 min intervals to ColumnD with a value (sum) of ColumnE.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"ColumnC", type date}, {"ColumnD", type time}}),
    times = Table.Buffer(Table.FromColumns({List.Times(#time(0, 0, 0), 288, #duration(0, 0, 5, 0))}, {"ColumnD"})),
    f = (tbl as table) as table =>
        [combine = Table.Sort(tbl & times, "ColumnD"),
        group = Table.Group(combine, "ColumnD", {{"ColumnE", each List.Sum([ColumnE])}})][group],
    g = Table.Group(types, {"ColumnA", "ColumnB", "ColumnC"}, {{"s", f}}),
    expand = Table.ExpandTableColumn(g, "s", {"ColumnD", "ColumnE"}),
    sort = Table.Sort(expand, {"ColumnA", "ColumnC", "ColumnD"})
in
    sort

[lejo54]

Hi. Ideally i would like to see a result like below. Black is source data. Red is added times and counts 0 

[AlienSx]

@lejo54 well, I did pretty much the same but Columns A through C are filled with original data (month, week and date). 

[lejo54]

I think i see what you made. You added all the missing 5 minut intervalls in the existing coulmn D right? That might work. That will add the "missing" time stamp in the source data. The summary in column E i can fix separatly. So, can i copy your formula and paste in my data table, and which part?

[AlienSx]

@lejo54 wow, easy, mate. It's not an Excel formula. It's Power Query forum and my code is just a set of M language expressions. This video helps to understand how to incorporate code from this forum into your solution. Also please mind different column names in your original picture and my code. I just simplified my life.

[lejo54]

Aha. I understand. I was sp set on finding i solution before PBI 🙂 I tried your solution, and did get it set up as a new query with the correct source name. I cant get it to recognize my column names in my sorce but thats and issue on my side. I will accept yours as the solution and figure out whats going on here. Thanks.