cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpattamthanam
Frequent Visitor

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.

 

PhotoJ.JPG

 

Every help in this regard is very much appreciated. Thanks in advance

 

JP

1 ACCEPTED SOLUTION

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"

View solution in original post

9 REPLIES 9
JW_van_Holst
Resolver III
Resolver III

Nice challenge!
I have solved the first column. Number 2 is copy paste. 

Picture3.png

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, C1D = _t, C2D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C1D", Int64.Type}, {"C2D", 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_argument", 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_argument])))
in
    #"Added Custom2"

Hi @jpattamthanam ,

 

Great! How about column 2?Have you also achieved column 2?If so,could you pls mark the reply as answered to let more people find the solution?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

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"

View solution in original post

justinh
Advocate III
Advocate III

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.  

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)

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. 

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. 

 

 

    

 

 

 

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

 

 

CNENFRNL
Community Champion
Community Champion

Hi, @jpattamthanam , in general, you may transform the target region (eg A1:B2 in your example) to list and use

List.Count(List.PositionOf( list , "substring" , Occurrence.All))

 to count the occurrence of a specific string.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors