Returning a value if a given value exists in range

[dids86]

Hi everyone,

New User here.

I’m trying to return a value in excel whereby if the Account Code HT0000 exists within the Transaction number range then Yes is returned against the Transaction Number cell, otherwise No is returned. I’ve included a screenshot below if anyone is able to advise.

A follow up query to this, can this be recreated in PowerQuery with DAX?



Thank you for your help

 

[arthurbr]

Try

=IF(COUNTIFS($A$2:$A$16,A2,$B$2:$B$16,"HT0000"),"yes","no")

in C1 and pull down

 

[RoryA]

can this be recreated in PowerQuery with DAX

Power Query doesn't use DAX, so no. You could do it in M code though, or in the data model in DAX.

 

[dids86]

Try

=IF(COUNTIFS($A$2:$A$16,A2,$B$2:$B$16,"HT0000"),"yes","no")

in C1 and pull down

This is brilliant thank you

 

[dids86]

Power Query doesn't use DAX, so no. You could do it in M code though, or in the data model in DAX.

Thanks, would you know of any way to create a Measure in Powerbi to return the same result or within Power Query Editor?

 

[RoryA]

I'm not sure that a measure would make sense for that, but perhaps a calculated column. You could do it in PQ in various ways. A simple one would be to create a query that just lists Transaction numbers where the code is HT0000 and then merge that with the main query. If the result is null, then the answer is no, if you get a table, the answer is yes. You could also use something like List.Count(List.Select(...)) as an equivalent to COUNTIF.

 

[dids86]

I'm not sure that a measure would make sense for that, but perhaps a calculated column. You could do it in PQ in various ways. A simple one would be to create a query that just lists Transaction numbers where the code is HT0000 and then merge that with the main query. If the result is null, then the answer is no, if you get a table, the answer is yes. You could also use something like List.Count(List.Select(...)) as an equivalent to COUNTIF.

That's great thank you