New column to add the amount of times a specific set of values appear in a row

[Anonymous]

Hello all, hoping someone could help me,

In the query editor I need to make a new column to count the amount of times 'C1', 'NA' and 'C2' appear in each row.

I've attached the desired outcome below, I can get it to work using a 'COUNTIF' function in excel but not sure how to do it through PowerBI.

Thanks!

[PSRai]

I currently have data where by its measuring usage of each electrical sockets, Energy by date time (seconds). I need to create a New Column as below. Unfortunately I cant do this in excel due to the amount of data 

Date/Time                                     Socket ID             Energy                     New Column

18/10/2019 - 13:01:01                      1                         0.1                               0.1
18/10/2019 - 13:01:05                      1                         0.3                               0.3
18/10/2019 - 13:01:07                      1                         0.5                               0.5
18/10/2019 - 13:01:09                      1                         0.6                               0.6
18/10/2019 - 13:01:11                      1                         1.0                               1.0

18/10/2019 - 13:01:15                      1                         0.0                               1.0
18/10/2019 - 13:01:18                      1                         0.3                               1.3
18/10/2019 - 13:01:20                      1                         0.5                               1.5
18/10/2019 - 13:01:22                      1                         0.7                               1.7

(Socket Switches off at 13:01:11 and switched back on at 13:01:15)

As you can see the Energy column reading increases at each reading from 18/10/2019 - 13:01:01 however when the socket is switched off the reading then sets to 0.0. what I need to do is bridge the gap. I need to create a new column as above and say *IF the current reading is less then the MAX reading then ADD MAX reading to current reading*. So in this case 1.0 is the MAX reading and lets say 0.0 is current I need to add 1.0 to 0.0.

also their are multiple sockets so it would look more like this,

Date/Time                           Socket ID          Energy        New Column

18/10/2019 - 13:01:01            1                     0.1                0.1
18/10/2019 - 13:01:01            2                     4.3                4.3
18/10/2019 - 13:01:01            3                     5.5                5.5
18/10/2019 - 13:01:01            4                     1.6                1.6
18/10/2019 - 13:01:01            5                     1.2                1.2

18/10/2019 - 13:01:15           1                     0.0                 1.0
18/10/2019 - 13:01:15           2                     0.1                 4.4
18/10/2019 - 13:01:15           3                     0.2                 5.7
18/10/2019 - 13:01:15           4                     0.0                 1.6
18/10/2019 - 13:01:15           5                     0.3                 1.5

Hope this makes sense?

[Greg_Deckler]

@ImkeF 

[ImkeF]

Hi @Anonymous ,

simply add a column with the following formula: 

List.Count(List.Intersect({Record.FieldValues(_), {"C1", "NA", "C2"}}))

[Anonymous]

Thanks for the response. Its not working unfortunatley. Attached is the actual powerbi format. But it doesnt seem to be adding all values.

[ImkeF · Accepted solution]

Sorry, that formula ate the duplicates.

Please use this instead:

List.Count(List.Select(Record.FieldValues(_), (x) => List.Contains({"C1", "NA", "C2"}, x))))