Forum Discussion
summarize columns by group without blank
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
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.
5 Replies
- amitchandak
Super User
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/
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
Community Champion
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.
