summarize columns by group without blank

[Anonymous]

Hello,

This is what my data set looks like: 

the sensors are measured every few milliseconds (Timestamps in epoch), Now they all fall in the 10-second bin but the timestamps are slightly different. What I want to achieve is that the blanks are gone, so that the date + time is no longer displayed multiple times , but just once every 10 seconds with all the correct values ​​behind it(without null).

Does anyone know the right approach to do do this?

Thank you

[amitchandak]

@Anonymous , Not sure I got it. See if this can help

https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/

[Anonymous]

Thank you for your response,

To make my problem clear, I made an example in excel:

This is what my data set looks like in powerBI:

So I have multiple measurements of certain elements within my 10 minutes, But I can't really compare them in 10 min bins because of the blanks.

This Is how I would like to see it: 

So I want to sort it by 10 min bins ignoring the blanks.

I hope this makes it more clear for you.

Thanks

[AllisonKennedy · Accepted solution]

As the others have suggested, grouping will mostly work but you will need to select advanced and choose the aggregation you want for each column (SUM, MAX, COUNT) for the TRUE/FALSE column this might be a bit tricky. 

Do the sensors always report in the same order? If Sales is always the last of the 10min, you could go into the Power Query/Transform Data and add a conditional column to mark that as the row to keep (IF Sales <> null then "Keep" Else "Remove") and then use the Fill Down option on all four sensor columns to replace the blanks. Finally filter the table for the new conditional column = "Keep"

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[mahoneypat]

In the query editor, just click on the Group By button and choose your bin column with a sum aggregation.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

This is what I did:

But the date bins are still duplicated: