Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 IV
Resolver IV

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"
justinh
Advocate IV
Advocate IV

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
Not applicable

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors