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

If a value exists more than once take one of the values.

For each type ABC, DEF, GHI,
If Body value exists more than once in 10 mins interval only take one of the values in that interval for each Type.

Below I have attached a sample data and the result I am expecting to get.

 

Sam9.PNG

 

The result I am expecting to get

LastSample.PNGLastSampleGraph.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: If a value exists more than once take one of the values.

Hi @Samrawit21

 

You can create a calculated table like this, where Table1 is the first table you show:

 

NewTable =
SUMMARIZECOLUMNS ( Table1[TimeInterval],
Table1[Type],
Table1[Airflow],
Table1[BodyValue] )

 

Note that this will work well if both Airflow and BodyValue are the same in the rows for the same ten minute interval and Type. Otherwise all different lines will be shown. For instance, take ABC at 10:40. Your data has three rows for it, all with Airflow=0 and BodyValue=1. Only one one row will be show in the new table. If you had instead two rows as shown and another one with for example Airflow=1 and BodyValue=1 the new table would show one row with Airflow=0 and BodyValue=1 and another with Airflow=1 and BodyValue=1  for ABC at 10:40       

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

 

Cheers

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: If a value exists more than once take one of the values.

Hi @Samrawit21

 

You can create a calculated table like this, where Table1 is the first table you show:

 

NewTable =
SUMMARIZECOLUMNS ( Table1[TimeInterval],
Table1[Type],
Table1[Airflow],
Table1[BodyValue] )

 

Note that this will work well if both Airflow and BodyValue are the same in the rows for the same ten minute interval and Type. Otherwise all different lines will be shown. For instance, take ABC at 10:40. Your data has three rows for it, all with Airflow=0 and BodyValue=1. Only one one row will be show in the new table. If you had instead two rows as shown and another one with for example Airflow=1 and BodyValue=1 the new table would show one row with Airflow=0 and BodyValue=1 and another with Airflow=1 and BodyValue=1  for ABC at 10:40       

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

 

Cheers

View solution in original post

Samrawit21 Helper IV
Helper IV

Re: If a value exists more than once take one of the values.

@AlB Thank you! 🙂

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors