May 31, 2021
Count Table Rows in Power BI
For Power BI version 2.76.5678.782 (December 2019).
When working with data in Power BI, we often create new custom tables using fields and measures from multiple tables. If we’re not careful we can quickly create redundantly large tables. Unfortunately, Power BI doesn’t have a way to show the size of such custom tables, but we can get that information with a little help of R visualization. Let’s look at two ways to check the number of rows of our table visualization.
Method 1: With R Installation
If you have R installed on your Desktop, you only need to open up the table in RStudio and check the table size. Follow the steps bellow.
Our sample table consists of 6 fields: some are fields from other tables and some are explicit measures.
We first change table visualisation to R visualisation: select the table and click R visualisation.
Table becomes blank like on the image bellow. We open up RStudio by selecting arrow on the right lower end of the pane.
RStudio window opens up, containing the message bellow.
Our table is stored the variable called dataset. We add the following line:
n = nrow(dataset)
We run the code and check the value for n.
Method 2: Without R Installation
We can use R even if we don’t have it installed. We won’t get the result on our Desktop, but we’ll see it once we publish the report to Service. The Service will process the R code and show us the result we need.
Again change the table to R visual and paste the code bellow:
df = data.frame(x=1, y=1, label=n)
ggplot(data=df, aes(x=x, y=y, label=label)) + geom_text(size=50) + theme_void()
Publish the report to Service. Once uploaded, visual will display the number of rows.