Forum Discussion

Dandiel's avatar
Dandiel
Frequent Visitor
7 years ago
Solved

Something similar to excels CountIF in Query Editor

Hi there,   first of all I am very grateful because I have found a lot of very helpful posts in this forum already, but for my current case I could´t find anything suitable.   So what I would lik...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Dandiel ,

 

Indeed if it reached 10GB after 35mins it can't be a good solution :smileyvery-happy: mybad then

 

I think this should work even better

let
    Source = Table,
    CostCenterToList = Table.ToList(Table.SelectColumns(Source,"Cost Center")),
    #"Add Column" = Table.AddColumn(Source,"PartnerCostCenterExists", each List.Contains(CostCenterToList,[Partner Cost Center]))in
    #"Add Column"

What am i doing : 

Convert the column "Cost Center" into a list so we can use List.Contains for each value of "Partner Cost Center" to determine if it exists. 

The only thing with this solution is that you only have a result as true false and not a count but i think that's what you want. If you need a count just tell me !

 

I hope this will work for you, i didnt try it for a huge data amount.

 

Regards, 

Etienne

Dandiel's avatar
Dandiel
Frequent Visitor
7 years ago

Hi ImkeF 

thank you very much for your input. It works just fine!

Only really small disadvantage is that I cannot really see what happens / which rows were excluded nor can I filter for it. But I am happy J

And in addition I have learned quite a lot about the merge function via your link.

 

Hi Anonymous 

thank you very much as well!

I have tried your new code. It creates the column within a couple of seconds just fine. Within the first 200 rows its only false. When I try to scroll down or filter using “load more” it starts to load. Again after around 30min of loading I stopped it. Your solution where I can actually see for each row if it needs to be excluded would be nice but performance wise the proposal of ImkeF seems to be the more efficient one.

In fact I hope I did everything correctly with your code so it´s not the case that your code is fine and the problem is with the user aka me.

 

So kudos to both of you and regards

Daniel