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 ,

 

Could you try this and let me know if you encounter any difficulty ? 

let
  Source = Table,
  #"JoinTables" = Table.NestedJoin(Source, {"PartnerCostCenter"}, Source, {"CostCenter"}, "newIsSomewhere", JoinKind.LeftOuter),
  #"CountResult" = Table.AggregateTableColumn(#"JoinTables", "newIsSomewhere", {{"CostCenter", List.NonNullCount, "Count"}})
in
  #"CountResult"

If you dont want to use the query editor just ask, I can explain you how to do it in a more simple way.

 

Regards, Etienne

Dandiel's avatar
Dandiel
Frequent Visitor
7 years ago

Hi Anonymous ,

 

thank you for your fast response!

 

I have tried your code. After I hit "done" in the advanced editor PBI has started to load. At the bottom right corner it started to count the mega bytes. After over 10 GB and 35min I canceled the calculation assuming something went wrong.

 

After that I deleted the #"CountResult" line in your code, hoping I can take a look at the joint table. So I clicked on a random "table" in the new column "newIsSomewhere" and PBI said it´s empty.

 

Do you have any idea why this is happening?

 

I don't think thats the reason but just in case for your information, the table consists of almost 200k rows.

 

And I don't mind using the editor or another option as long as it works properly :-)

 

Thanks and regards, Daniel

  • 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

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous ,

    you would help us answerers if you could post some sample data with before data and desired result.

    Possible reasons for empty results are deviations in cases (as Power Query is case sensitive) or unprintable characters (use Text.Trim and Text.Clean before).

     

    • Dandiel's avatar
      Dandiel
      Frequent Visitor
      7 years ago

      Hi ImkeF ,

      I assume you wanted to address me with your post.

      I have edited my first post and provided sample data.

      Thanks for trying to help me out.

      Daniel