locked
Total row in table result from pivot RRS feed

  • Question

  • I have a table result from Power Query using a pivot to generate columns.  I now need a total row in the resultant table that does a COUNTIF(ThisColumn, MyString).   COUNTIF works fine until the data updates and the headers change.  How can I accomplish this?

    Thanks

    Brian

    Thursday, August 8, 2019 7:54 PM

Answers

  • Hi,

    If you need exactly this format:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        group = Table.Group(Table.SelectRows(Source, each [Use] = "Left"), {"Type"}, {"cnt", Table.RowCount}),
        transpose = Table.AddColumn(Table.PromoteHeaders(Table.Transpose(group)), "EventID", each "Total"),
        final = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Use") & transpose
    in
        final


    Friday, August 9, 2019 12:53 PM

All replies

  • Excel 2010 to 2019 Power Query (aka Get & Transform)
    Do the equivalent of CountIf() in PQ with GroupBy().
    Recommend never combining PQ/PP with vintage formulas.
    http://www.mediafire.com/file/n6mhamm5hrbs4i5/08_08_19.xlsx/file
    http://www.mediafire.com/file/r29v9ur88oxnwn1/08_08_19.pdf/file

    Thursday, August 8, 2019 10:32 PM
  • I did not explain my problem well.

    For example, after the pivot I need a totals row that only counts certain values.  Please advise.

    Friday, August 9, 2019 11:41 AM
  • I don't understand where you want the total but if you want to do it in PQ, this should point you in the right direction:

    List.Count(List.Select( PreviousStep[ColumnThatContainsLeft] ,each _="Left"))

    Friday, August 9, 2019 12:32 PM
  • Hi,

    If you need exactly this format:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        group = Table.Group(Table.SelectRows(Source, each [Use] = "Left"), {"Type"}, {"cnt", Table.RowCount}),
        transpose = Table.AddColumn(Table.PromoteHeaders(Table.Transpose(group)), "EventID", each "Total"),
        final = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Use") & transpose
    in
        final


    Friday, August 9, 2019 12:53 PM