Average and filter 1 minute data into 10 minutes data

[Cado_one]

Hi,

First, please apologies my bad english spoken, I'll do my best to explain my issue but if something is not clear don't hesitate to ask me.

My data is an imported csv file containing 736 000 lines and each line corresponds to a data record by minute. Here is an overview of the table in power query editor :

As you can see, each line corresponds to 1 minute. What I need is to average PAC1 PAC2 PAC3 columns value on 10 minutes and to have on each line the data column like 01/01/2020 09:00:00, on the following line 01/01/2020 09:10:00 and the next 01/01/2020 09:20:00 ... with the PAC values averaged on the minutes from 0 to 9.

I saw some guys on other topics doing it using dax formulas in power bi but for some reasons i have to do it in Power Query Editor.

If someone has an idea to help me with that I'd be very glad to hear it !

Thanks in advance,

Cado

[Smauro]

Hi @Cado_one,

A quick solution could be some fake time intelligence, so you'll end up with a new column [Date] which only has 10 minute intervals:

    #"Insert Date" = Table.AddColumn(PreviousStep, "Date", each 
        Date.ToText(DateTime.Date([data])) & " " & 
        Text.PadStart(Number.ToText(Time.Hour([data])), 2, "0") & ":" & 
        Text.Start(Text.PadStart(Number.ToText(Time.Minute([data])), 2, "0"), 1) & "0:00", type text),
    #"Correct Date Type" = Table.TransformColumnTypes(#"Insert Date",{{"Date", type datetime}}),

From where you could just aggregate your table based on this new column:

    #"Group by Date" = Table.Group(#"Correct Date Type", {"Source.Name", "Date", "tracker"}, {{"PAC1", each List.Average([PAC1]), type number}, {"PAC2", each List.Average([PAC2]), type number}, {"PAC3", each List.Average([PAC3]), type number}}),

However, since I suppose your table has more columns, the grouping should be done by you, choosing all the columns you want to keep. It can be quickly done using the GUI.

Cheers,

Spyros

[AlB]

Hi @Cado_one 

You can add a custom column with this code, which will determine the 10-min group each [data] value belongs to:

= [data] - #duration(0,0,Number.Mod(Time.Minute([data]),10),0)

and then just do a Group By based on that custom column (and Source.Name and tracker if necessary)  including the average of the PAC cols 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Cado_one]

@AlB @Smauro 

Thank you very much for your help, I tried the Smauro solution and it worked almost perfectly, at the exception that the average is not correct when some of the minutes in the decade are 0 and the others are not zero. 

I will work on it next monday and I'll write a message to inform you and other users for the solution before closing the topic.

If you have an idea of what could be the problem I'm open to read from you.

Thanks again, 

Cado

[Smauro · Accepted solution]

Hi @Cado_one,

I'm not sure I understand completely, but if you always wish for the 'average' to be all the values of the ten minute interval divided by 10, then you could simply change the:

List.Average([PACx])

to

List.Max({List.Sum([PACx]), 0}) / 10

Where List.Max is used to avoid nulls, assuming you do not have negative values.

Cheers

Edit: I believe @AlB's transformation is quicker and better resource wise than mine, so your code should be better looking like that:

#"Insert Date" = Table.AddColumn(PreviousStep, "Date", each 
        [data] - #duration(0,0,Number.Mod(Time.Minute([data]),10),0), type datetime),

#"Group by Date" = Table.Group(#"Insert Date", {"Source.Name", "Date", "tracker"}, {{"PAC1", each List.Max({List.Sum([PAC1]), 0}) / 10, type number}, {"PAC2", each List.Max({List.Sum([PAC2]), 0}) / 10, type number}, {"PAC3", each List.Max({List.Sum([PAC3]), 0}) / 10, type number}}),

[Cado_one]

@Smauro @AlB 

Thank you very for your help, both of your solutions are working. 

Because the solution is exposed on several posts, I will put this one accepted for solution and I invite each reader looking for help to read all the posts below.

Have a nice monday !

Cado

[AlB]

Hi @Cado_one 

Can you share the csv file so that we can work directly on it? Preferably the full one but at least a few dozens rows that have several 10-minute groups that have to be put together. You can share the file by PM if you do not want to publish it here

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

By the way you want three columns with the average as described, one for PAC1, one for PAC2 and one for PAC3? OR do you need one resulting column with the average of the three? If it is the latter please explain exactly how  

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers