How to convert countifs from XL to power query

[Taufik_8774]

Dear sir,

kindly help convert "COUNTIFS" from excel to power BI,

Sample for below data

I use formula on column Sequence, cell C2=COUNTIFS($A$1:$A2;$A2;$B$1:$B2;$B2) then I drag-down to all collumn below.

if serial No and StageName is same, then sequence will count 1, 2, etc...

[Anonymous · Accepted solution]

Hi @Taufik_8774 

Please try this:

First of all, I create a set of sample:

Then Transform Data > Add column > Index column

Then create a calculate column:

Sequence =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[SerualNo] = EARLIER ( 'Table'[SerualNo] )
            && 'Table'[StageName] = EARLIER ( 'Table'[StageName] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"SerialNo", "StageName"}, {{"Sequence", each Table.AddIndexColumn(_,"Seq",1,1)}}),
    #"Expanded Sequence" = Table.ExpandTableColumn(#"Grouped Rows", "Sequence", {"Seq"}, {"Seq"})
in
    #"Expanded Sequence"

Hope this helps.

[Taufik_8774]

Sorry sir, this difficult to understand by me.

I'm new in power BI

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"SerialNo", "StageName"}, {{"Sequence", each Table.AddIndexColumn(_,"Seq",1,1)}}),
    #"Expanded Sequence" = Table.ExpandTableColumn(#"Grouped Rows", "Sequence", {"Seq"}, {"Seq"})
in
    #"Expanded Sequence"

Hope this helps.

[Anonymous · Accepted solution]

Hi @Taufik_8774 

Please try this:

First of all, I create a set of sample:

Then Transform Data > Add column > Index column

Then create a calculate column:

Sequence =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[SerualNo] = EARLIER ( 'Table'[SerualNo] )
            && 'Table'[StageName] = EARLIER ( 'Table'[StageName] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Taufik_8774 · Accepted solution]

Thank You so much, this formula really work properly. Thanks sir  v-zhengdxu-msft 

[tharunkumarRTK]

@Taufik_8774 

Please find my solution 

let
    Source = Table.FromColumns({{"1A2103XN000251","1A2109XN001414","1A2109XN001414","1A2404PP034406","1A2404PP034406","1A2404PP034406","1A2404PP034406","1A2404PP040883","1A2404PP040883","1A2404PP040883"},{"Imager Alignment 1","Imager Alignment 1","Imager Alignment 1","Functional 2","Functional 2","Calibration 3","Calibration 3","Imager Alignment 1","Functional 2","Calibration 3"}}, {"SerialNo","StageName"}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each _),
    CountIfs = Table.FromColumns(List.Accumulate( #"Added Custom"[Custom], {{},{}}, (s,c)=> { s{0} & { [ SerialNo=c[SerialNo], StageName = c[StageName]  ]},  s{1} & { 
List.Sum(List.Accumulate(s{0}, {0}, (s1,c1)=> if c1[SerialNo] = c[SerialNo] and c1[StageName] = c[StageName] then s1 & {1} else s1 & {0}    ))  + 1
 } } ), {"Data", "Count"}),
    #"Expanded Data" = Table.ExpandRecordColumn(CountIfs, "Data", {"SerialNo", "StageName"}, {"SerialNo", "StageName"})
in
    #"Expanded Data"

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun