Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello community,
I struggle with the following request:
I count the occurence of a certain value (here 50) in multiple columns with this query
let
source =
#table(
{"Width A", "Width B", "Width C"},
{{50, 50, 50}, {40, 50, 50}, {40, 40, 40}, {45, 40, 40}}
),
#"Changed Type" = Table.TransformColumnTypes(source,{{"Width A", Int64.Type}, {"Width B", Int64.Type}, {"Width C", Int64.Type}}),
#"Count 50&45" = Table.AddColumn(#"Changed Type", "Count", each
List.Count(
List.Select(
Record.ToList(
Record.SelectFields(_, {"Width A", "Width B", "Width C"})
), each _ = 50
)
)
)
in
#"Count 50&45"
this shall now also count the number 45, so that the fourth row in the example returns 1
How can I achieve this? It sounds so simple, but I can't figure it out...
thanks a lot in advance
Solved! Go to Solution.
OMG... unbelievable... I knew the solution would be simple, but these syntax errors always give me rest...
Thanks so much.
Tip: If you need to check for more than just a couple of values, you can use List.Contains instead of a bunch of comparisons combined with "or".
For example, instead of
each _ = 50 or _ = 49 or _ = 48 or _ = 47 or _ = 46 or _ = 45
write the following:
each List.Contains({50,49,48,47,46,45}, _)
Thanks, @AlexisOlson , I was looking for something similar to the IN statement in DAX, but wasn't succesfull.
This defenitely helps!
Change each _ = 50 to
each _ = 50 or _ = 45
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
27 | |
12 | |
12 | |
11 | |
9 |
User | Count |
---|---|
53 | |
28 | |
17 | |
14 | |
13 |