Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Sorry, that formula ate the duplicates.
Please use this instead:
List.Count(List.Select(Record.FieldValues(_), (x) => List.Contains({"C1", "NA", "C2"}, x))))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
Hi @Anonymous ,
simply add a column with the following formula:
List.Count(List.Intersect({Record.FieldValues(_), {"C1", "NA", "C2"}}))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for the response. Its not working unfortunatley. Attached is the actual powerbi format. But it doesnt seem to be adding all values.
Sorry, that formula ate the duplicates.
Please use this instead:
List.Count(List.Select(Record.FieldValues(_), (x) => List.Contains({"C1", "NA", "C2"}, x))))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |