none
CountIf using power query RRS feed

  • Question

  • Hi All 

    using M i am trying to add a custom column that will count how many times a text value appears in the column without having to group by . (both count and value are in the same column)

    I have tryed this :

    List.Count(List.FindText(Source[Column1], [Column2]))

    from Stackoverflow.com/questions/35351610/how-to-do-countifs-sumifs-in-powerquery-m-language-formulas

    submited by  user Imkef   

    but i get  error as a result  .

    Any help will be greatly appreciated .

    Thank you 

     

    Saturday, February 18, 2017 4:10 PM

Answers

All replies

  • Imke's formula works if:

    1) You have a table with column names Column1 and Column2
    2) Column2 has the text that you want to count in Column1 (in part or in whole)
    3) The previous step before you add the custom column is named Source

    If your scenario is different from the above, could you provide the details?

    • Marked as answer by PQAK Friday, February 24, 2017 7:57 AM
    Saturday, February 18, 2017 5:13 PM
  • Thank you for your reply Colin i really appreciate your time in answering this.

    My error was that i was using the same column for both conditions (column1 &column2)

    I though i could avoid duplicating the column i need to count. 

    Thank you again great help.

     
    Saturday, February 18, 2017 8:23 PM
  • Excel 2010 Power Query
    With count and value in the same column.
    With COUNTIF and COUNTIFS.
    With text and/or numbers.
    With PQ custom function.
    http://www.mediafire.com/file/nrjnl0w1p4bbt8l/02_20_17a.xlsx

    Excel 2016 Pro Plus
    http://www.mediafire.com/file/8yjkn5aggyaedlo/02_20_17b.xlsx

    • Edited by Herbert Seidenberg Monday, February 20, 2017 11:49 PM
    • Marked as answer by PQAK Friday, February 24, 2017 7:57 AM
    Monday, February 20, 2017 11:39 PM
  • Excellent job! I was looking for the SUMIFS as well, do you also have a function like this?

    Thanks in advance.

    Wednesday, April 12, 2017 3:55 AM
  • I downloaded your spreadsheet for Excel 2016.  I don't know how to implement this however.  Can you provide some guidance?
    Thursday, October 5, 2017 3:16 PM
  • Alex,
    Excel 2016 Pro Plus with Power Query (aka Get & Transform)
    Added easier version with Group()
    Added notes.
    http://www.mediafire.com/file/8yjkn5aggyaedlo/02_20_17b.xlsx

    Friday, October 6, 2017 3:24 AM
  • I'm trying to use your example, but do not understand how to create the Query as a Function for Count_IF.  I see how you can right-click on a query in the Query Editor and create a function, but if I use what you have in your Count_IF, and paste that, I get an error.

    Please advise.  Thanks!


    Rodger Benavides

    Friday, October 6, 2017 9:01 PM
  • Rodger,
    Use the alternate version.
    It does not use functions and is easier to implement.
    I would be hard pressed to recall what I did months ago.

    Saturday, October 7, 2017 1:32 AM
  • take a look to my example in here:

    https://stackoverflow.com/a/48782135/9187705

    Wednesday, February 14, 2018 8:07 AM
  • Excellent Work Herbert....
    Your solution is always life saving. Hahhaaa.. Keep it up
    Monday, March 4, 2019 9:37 AM