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"
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),