Count rows in all other queries and return as a single M query?

[sasdfasdfsad]

Is there a way to add a query in Power Query in Transform Data for Power BI where it will count the rows in all the other queries in the data set?

Ideally it would be a way to iterate through and return all the row counts for all the queries.  I'd like to have a way to see row counts after a data refresh all in one location (one query).

Thank you

[ToddChitt · Accepted solution]

Do you absolutely need this in Power Query? DAX can do a simple COUNTROWS measure.

But if you insist (and I do NOT recommend):

Duplicate one of the tables. Add a Custom Column with a literal value as the name of the table, like "Table 1". Now do a GROUP BY on that column, and Aggregate of COUNT. Do this for all tables. Finally, do a UNION of these tables (assuming all the column names are identical.

WHY is this approach NOT recommended? Because it DOUBLES your query load! You have to read each table source TWICE!

In DAX, you could try this: Create a DAX table using SUMMARIZE function that does a COUNTROWS. The add to that a ADDCOLUMNS DAX function. Finally, wrap this in a DAX UNION statement with other SUMMARIZE statements. 

Sample DAX table that counts rows in 'Table1':

Table Counts  = ADDCOLUMNS(SUMMARIZE('Table 1', "My Count", COUNTROWS('Table 1')),"Table Name", "Table 1")

I leave it to you to investigate the use of the UNION DAX function.

[watkinnc · Accepted solution]

You could just do 

= Table.RowCount(Query1)+Table.RowCount(Query2)+...+Table.RowCount(QueryN)

--Nate

[ronrsnfld · Accepted solution]

As a single line of M-Code:

List.Accumulate(
        Record.FieldNames(#sections[Section1]),
        0,
        (x,y)=> try x + Table.RowCount(Expression.Evaluate(y,#shared)) otherwise x)

Depending on how you are using this, you may need to subtract Table.RowCount(#"Previous Step") from the value.

#sections[Section1] returns a list of the queries as a record

The try ... otherwise is to handle those queries that might not be returning Tables.

[watkinnc · Accepted solution]

You could just do 

= Table.RowCount(Query1)+Table.RowCount(Query2)+...+Table.RowCount(QueryN)

--Nate

[ToddChitt · Accepted solution]

Do you absolutely need this in Power Query? DAX can do a simple COUNTROWS measure.

But if you insist (and I do NOT recommend):

Duplicate one of the tables. Add a Custom Column with a literal value as the name of the table, like "Table 1". Now do a GROUP BY on that column, and Aggregate of COUNT. Do this for all tables. Finally, do a UNION of these tables (assuming all the column names are identical.

WHY is this approach NOT recommended? Because it DOUBLES your query load! You have to read each table source TWICE!

In DAX, you could try this: Create a DAX table using SUMMARIZE function that does a COUNTROWS. The add to that a ADDCOLUMNS DAX function. Finally, wrap this in a DAX UNION statement with other SUMMARIZE statements. 

Sample DAX table that counts rows in 'Table1':

Table Counts  = ADDCOLUMNS(SUMMARIZE('Table 1', "My Count", COUNTROWS('Table 1')),"Table Name", "Table 1")

I leave it to you to investigate the use of the UNION DAX function.