Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Need Help on sumif & Countif

Hi All I have mention below data. Need to apply  formula to get the data. Concatenate Reference and ABS(Amount doc currency) in input sheet - Copy and Paste special in a new sheet. Apply sumif for...
Need Help
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    KuntalSingh, go ahead and try it, and if you hit any snags, just reach out.

     in power query, create a new column

     

    = Table.AddColumn(YourTableName, "ConcatenatedColumn", each [A] & Number.ToText(Number.Abs([B])))

     

    SUMIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"SumColumn", each List.Sum([B]), type number}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"SumColumn"}, {"SumColumn"})
in
    ExpandedTable

     

    COUNTIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"CountColumn", each Table.RowCount(_), Int64.Type}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"CountColumn"}, {"CountColumn"})
in
    ExpandedTable

     

    COUNTIF MR8M:

     

    = Table.AddColumn(YourTableName, "CountMR8M", each if [D] = "MR8M" then 1 else 0)

     

    sum count

     

    let
    SummedTable = Table.Group(YourTableName, {}, {{"TotalMR8M", each List.Sum([CountMR8M]), type number}})
in
    SummedTable

     

    :in power query create custom column:

     

    = Table.AddColumn(YourTableName, "Condition1", each if [D] = 3 and [E] > 1 then "True" else "False")

     

    create custom column

     

    = Table.AddColumn(YourTableName, "Condition2", each if Number.Mod([B], 2) = 0 and [F] = 0 and [G] = 0 then "True" else "False")

     

     

    Did I answer your question? If so, please mark my post as the solution!
    Your Kudos are much appreciated! Proud to be a Resolver III !

ahadkarimi's avatar
ahadkarimi
Solution Specialist
2 years ago

Hey KuntalSingh, try this and let me know if you encounter any issues.


In a new sheet:

=A2 & ABS(B2)

 

=SUMIF($C$2:$C$100, C2, $B$2:$B$100)

 

=COUNTIF($C$2:$C$100, C2)

 

=COUNTIF(D:D, "MR8M")

 
Home-> Conditional Formatting -> New Rule:

=AND($D2=3, $E2>1)

 

  Home-> Conditional Formatting -> New Rule:

 

=AND(MOD($B2, 2)=0, $F2=0, $G2=0)

 

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago

Thanks for relpy 

I am able to do it in excel but need same in power query Need M code.

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    KuntalSingh, go ahead and try it, and if you hit any snags, just reach out.

     in power query, create a new column

     

    = Table.AddColumn(YourTableName, "ConcatenatedColumn", each [A] & Number.ToText(Number.Abs([B])))

     

    SUMIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"SumColumn", each List.Sum([B]), type number}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"SumColumn"}, {"SumColumn"})
in
    ExpandedTable

     

    COUNTIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"CountColumn", each Table.RowCount(_), Int64.Type}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"CountColumn"}, {"CountColumn"})
in
    ExpandedTable

     

    COUNTIF MR8M:

     

    = Table.AddColumn(YourTableName, "CountMR8M", each if [D] = "MR8M" then 1 else 0)

     

    sum count

     

    let
    SummedTable = Table.Group(YourTableName, {}, {{"TotalMR8M", each List.Sum([CountMR8M]), type number}})
in
    SummedTable

     

    :in power query create custom column:

     

    = Table.AddColumn(YourTableName, "Condition1", each if [D] = 3 and [E] > 1 then "True" else "False")

     

    create custom column

     

    = Table.AddColumn(YourTableName, "Condition2", each if Number.Mod([B], 2) = 0 and [F] = 0 and [G] = 0 then "True" else "False")

     

     

    Did I answer your question? If so, please mark my post as the solution!
    Your Kudos are much appreciated! Proud to be a Resolver III !

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      2 years ago

      Thank you very much for yor kind support!