Forum Discussion

walnei's avatar
walnei
Helper III
8 years ago
Solved

Countif POWER QUERY


Hello, could you help me.
I need to do in Power Query, a column with the total number of times a record appears in the key column. I need it to be in the power query and not in the dax.
It would be like a countif in excel

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    8 years ago

    walnei,

     

    This works in my sample, maybe it will work for you?

     

    RAND(1,10)
    7
    2
    1
    2
    9
    5
    7
    8
    8
    2
    3
    1
    6
    2
    5
    6
    5
    3
    4
    3
    10
    3
    10
    6
    7
    4
    9
    1
    5
    6

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\Book99.xlsx"), null, true),
    Tbl_count_Table = Source{[Item="Tbl_count",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tbl_count_Table,{{"RAND(1,10)", Int64.Type}}),
    
    // Group 'All Rows' in the UI interface generates #"Grouped Rows" see image below
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RAND(1,10)"}, {{"GroupTables", each _, type table}}),
    
    // Counts the rows in the table from previous step
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables]))
in
    #"Added Custom"

     

    Forgot to add the result: You can just remove the column you don't need.

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    8 years ago

    walnei,

     

    This works in my sample, maybe it will work for you?

     

    RAND(1,10)
    7
    2
    1
    2
    9
    5
    7
    8
    8
    2
    3
    1
    6
    2
    5
    6
    5
    3
    4
    3
    10
    3
    10
    6
    7
    4
    9
    1
    5
    6

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\Book99.xlsx"), null, true),
    Tbl_count_Table = Source{[Item="Tbl_count",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tbl_count_Table,{{"RAND(1,10)", Int64.Type}}),
    
    // Group 'All Rows' in the UI interface generates #"Grouped Rows" see image below
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RAND(1,10)"}, {{"GroupTables", each _, type table}}),
    
    // Counts the rows in the table from previous step
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables]))
in
    #"Added Custom"

     

    Forgot to add the result: You can just remove the column you don't need.