Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm struggling to find out how to run CountIf Formula in Power Query. The purpose is to count a certain value in 2 or more column. Here's the example.
Thank you
I don't know how to help you without your code and data. Most likely you don't know how to apply my code. This code works just fine:
let
your_table = #table(
{"name", "01-apr", "02-apr", "03-apr", "04-apr"},
{{"a", "P", "P", "P", "S"},
{"b", "S", "P", "P", "P"},
{"c", "S", "S", "P", "P"}}
),
total = Table.AddColumn(
your_table,
"TOTAL",
each List.Count(
List.PositionOf(
Record.FieldValues(_),
"P",
Occurrence.All
)
)
)
in
total
regarding underscore: watch this
Table.AddColumn(
your_table,
"TOTAL",
(x) => List.Count(
List.PositionOf(
Record.FieldValues(x),
"P",
Occurrence.All
)
)
)
What is "x" in here?
x is function parameter (variable) taking a value of current row of the table (in the form of record). Use code below if you feel comfortable to use each
Table.AddColumn(
your_table,
"TOTAL",
each List.Count(
List.PositionOf(
Record.FieldValues(_),
"P",
Occurrence.All
)
)
)
Sorry i still don't understand, the result was error, i think it's because this part. Can you help me? and why using underscore in record field values below
Record.FieldValues(_), "P", Occurrence.All
Thank you so much