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

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