none
Table.RowCount in a "Collapsed" Table RRS feed

  • Question

  • Hi All,

    My goal is to get a row count for each of the "nested" sales tables shown below, without having to expand each actual table.  I am programming it right now to open each table to get counts (within the same query) but I thought maybe there is a more efficient way.  Any ideas are welcome, thank you!

    Jake


    Name Data Item Kind Hidden
    Overview Table Overview Sheet FALSE
    Performance Table Performance Sheet FALSE
    Charts Table Charts Sheet FALSE
    Finished Table Finished Sheet FALSE
    Pipeline Table Pipeline Sheet FALSE
    Lost Table Lost Sheet FALSE
    Hold Table Hold Sheet FALSE
    Not Active Table Not Active Sheet FALSE

    Wednesday, August 30, 2017 4:25 PM

Answers

  • Simple. You can add a custom column through the Query Editor UI. Formula shown in bold below.

    #"Added Custom" = Table.AddColumn(PreviousStepName, "Rows Count", each Table.RowCount([Data]))

    Edit:

    Seeing that your tables are Excel files, you need to promote headers before the #"Added Custom" step. See your follow up post for details.


    • Marked as answer by Jake Burns Wednesday, August 30, 2017 4:44 PM
    • Edited by Colin Banfield Wednesday, August 30, 2017 4:52 PM
    Wednesday, August 30, 2017 4:37 PM

All replies

  • Simple. You can add a custom column through the Query Editor UI. Formula shown in bold below.

    #"Added Custom" = Table.AddColumn(PreviousStepName, "Rows Count", each Table.RowCount([Data]))

    Edit:

    Seeing that your tables are Excel files, you need to promote headers before the #"Added Custom" step. See your follow up post for details.


    • Marked as answer by Jake Burns Wednesday, August 30, 2017 4:44 PM
    • Edited by Colin Banfield Wednesday, August 30, 2017 4:52 PM
    Wednesday, August 30, 2017 4:37 PM
  • Wow,

    Easy it was.  I did not know you could run Table formula without expanding it.  THANKS Colin!

    Jake

    Wednesday, August 30, 2017 4:45 PM
  • Right. Since the value in each row is a table, you can perform any table operation on the value.
    Wednesday, August 30, 2017 5:08 PM