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 like to do within the query editor:

 

I have one table called "Cost Center":

Columns:

A Cost element (text)

B Cost Center (text) e.g. 0008/123456, dublicates existing

C Partner Cost Center (text) e.g. 0007/456789 dublicates existing

D Value (decimal number)

 

EDIT:

 

Next to these columns there are a coulple of other columns but I guess they are irrelevant.

 

Basicly what I aim for is a new colum E which tells me for each row if the Partner Cost Center is somewhere - not exlicitly in the same row - in the column "Cost Center". In a second step I would like to exclude these rows from my model using the standard filter option. 

 

So the new colum could say "true" or just count and I would then filter for 0.

In Excel I would use Countif(B:B;C1) and copy paste it for each row.

 

I have read a lot of posts about CountIf in Dax or CountX and list.contain in this forum but I couldn´t transfer it to my case.

 

Any help is appreciated. 

Thanks and regards

Daniel

Need Help

7 Replies

  • 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