Oct 15 2021 11:11 AM
Hi dears
in no. of working machines cell (orange), I need to count how many machine working from (yellow cells) with condition that the number should be more than 95! how can i do this in power query?
could anyone help?
Note: I make it in Excel with this equation =COUNTIF([@[KX01]:[KX05]],">95")
Oct 24 2021 12:03 PM
It's hard to recognize something on such screenshot. For the model like this
COUNTIF() could be
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Inserted Count" = Table.AddColumn(
Source,
"Count",
each List.NonNullCount(
List.Transform( {[A], [B], [C], [D], [E]},
each
if List.Sum( {_, 0} ) > 95
then _
else null
)
),
Int64.Type)
in
#"Inserted Count"
Please check in attached file.
Nov 12 2021 10:17 AM
Nov 12 2021 01:56 PM
It depends on your Source. You may copy/paste below as new query into Power BI Desktop, it shall work
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(Binary.FromText("i45WKqkoMVTSUTI0AJFwZGhgCiSL83NTDZVidcCqjMDCENIYSJqbw9UZGphBVRvBVINUGJkiGwZRZAgz1xim0gQsZwJXZwZSZgDmWkDVmijFxgIA",
BinaryEncoding.Base64),
Compression.Deflate)),
{"T","A","B","C","D","E","U"}
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"A", type number},
{"B", type number},
{"C", type number},
{"D", type number},
{"E", type number}}
),
#"Inserted Count" = Table.AddColumn(
#"Changed Type",
"Count",
each List.NonNullCount(
List.Transform( {[A], [B], [C], [D], [E]},
each
if List.Sum( {_, 0} ) > 95
then _
else null
)
),
Int64.Type)
in
#"Inserted Count"