Forum Discussion

jpattamthanam's avatar
jpattamthanam
Frequent Visitor
5 years ago
Solved

A simple way for COUNTIF function in power query

Hello Community, I have a data set as shown below. Is there a way to have this done in power query editor. Column A and B is the Data input and Column C and D are the results expected.     ...
Need Help
  • JW_van_Holst's avatar
    JW_van_Holst
    5 years ago

    Second column with countif added. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYkMwjtWBiMBEjcEipkCWKVTUCEXEBIzBugwMQCZYWqKYZQFiggmEXoQYVHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, C1Desired = _t, C2Desired = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C1Desired", Int64.Type}, {"C2Desired", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "SUMIF_1_argument", 
        each if [Index] = 0 
        then 
            {AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}
        else 
            {AddedIndex{[Index]-1}[C1], AddedIndex{[Index]-1}[C2],
            AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "SUMIF_2_argumentC1", each #"Added Custom"{[Index]}[C1]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ResultC1", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC1]))),
    //second column
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "SUMIF_2_argumentC2", each #"Added Custom"{[Index]}[C2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "ResultC2", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC2])))
in
    #"Added Custom4"
justinh's avatar
justinh
Advocate IV
5 years ago

Use "Group by" on column A, and choose "Count" as the metric, but click on "advanced" and add another metric, call this one "All" and select "All Rows" as the metric. 

After the Group is complete, expand "All" to get your original rows back, but now with the Row Count column. Do the same for Column B and you are done.  

Anonymous's avatar
Anonymous
Not applicable
5 years ago

But are the duplicates to be counted only those in the ranges of two rows and two columns? For example, the 3 in the second line comes from = COUNTIF ($ A2: $ B3; $ A3) the 1 of the sixth line comes from = COUNTIF ($ A5: $ B6, $ A6)

  • justinh's avatar
    justinh
    Advocate IV
    5 years ago

    Ah! I get it. 

    You are right, my idea won't work. 

     

    I'm brainstorming what could work, but right now I've got nothing. 

    • justinh's avatar
      justinh
      Advocate IV
      5 years ago

      Their are a few functions that come to mind, maybe a combination of them:

       

      //Table.AddIndexColumn() will add a column that you can use as a row reference.

      AddRowCount = Table.AddIndexColumn(PreviousStep, "RowNumber", 1,1)

       

      _List1 = AddRowCount[ChannelDWG1] // will create a list of the first column.

      _List2 = AddRowCount[ChannelDWG2] // will create a list of the second column.

       

      // List.FirstN(List, #) will reduce a list to that number of entries.

      _List1Short = List.FirstN(_List1, each [RowNumber])

      _List2Short = List.FirstN(_List2, each [RowNumber])

       

      //List.Combine() will make them both one long list

      _ListLong = List.Combine({_List1Short, _List2Short})

       

      //List.Select() will filter the list down to just the entries you want

      _ListSelect = List.Select(_ListLong, each [ChannelDWG1])

       

      //List.Count() will count the rows in the list, aka how many rows in the combined list matched your criterea

      _ListCount = List.Count(_ListSelect)

       

      Something like that, correcting for the obvious formatting errors. 

       

       

          

       

       

       

      • justinh's avatar
        justinh
        Advocate IV
        5 years ago

        Something like this

         

        AddRowCount = Table.AddIndexColumn(PreviousStep, "RowNumber", 1,1),

AddDWG1Dupes = 
    let
        _List1 = List.Buffer(AddRowCount[ChannelDWG1]), 
        _List2 = List.Buffer(AddRowCount[ChannelDWG2]), 
        _List1Short = List.FirstN(_List1, each [RowNumber]),
        _List2Short = List.FirstN(_List2, each [RowNumber]),
        _ListLong = List.Combine({_List1Short, _List2Short}),
        _ListSelect = List.Select(_ListLong, each [ChannelDWG1]),
        _ListCount = List.Count(_ListSelect)
    in
        Table.AddColumn(AddRowCount, _ListCount),

AddDWG2Dupes = 
    let
        _List1 = List.Buffer(AddRowCount[ChannelDWG1]), 
        _List2 = List.Buffer(AddRowCount[ChannelDWG2]), 
        _List1Short = List.FirstN(_List1, each [RowNumber]),
        _List2Short = List.FirstN(_List2, each [RowNumber]),
        _ListLong = List.Combine({_List1Short, _List2Short}),
        _ListSelect = List.Select(_ListLong, each [ChannelDWG2]),
        _ListCount = List.Count(_ListSelect)
    in
        Table.AddColumn(AddDWG2Dupes, _ListCount),