cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Average and filter 1 minute data into 10 minutes data

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 :

Capture.PNG
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

1 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}}),

 




Feel free to connect with me:
LinkedIn

View solution in original post

6 REPLIES 6
Super User I
Super User I

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




Feel free to connect with me:
LinkedIn

Super User III
Super User III

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 

 

SU18_powerbi_badge

   

@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

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}}),

 




Feel free to connect with me:
LinkedIn

View solution in original post

@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

Super User III
Super User III

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 

SU18_powerbi_badge

 

 

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors