cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dariuszszyc Frequent Visitor
Frequent Visitor

Row count at each step in Query (Power BI Desktop)

Hello,

 

I'm building a query based on large excel files.

When there are more than 999 rows I see an information like "30 COLUMNS, 999+ ROWS" after each step in my Query.

Imagine, that my query has about 50 steps, merging multiple tables.

 

How can I check the total rows count at each step of query ? This is the only way for me to verify that the merging didn't duplicate rows.

 

Adding separate column for indexing sounds a little silly because I'd still have to sort the list descending to see the largest index etc. I can't imagine doing that at each step to verify my calculations. It should be simply-accessible information - honestly - it's one of the most important information when working with databases IMO.

 

Is there a way I can get that information at each step of my query ?

 

Regards.

Darek

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Row count at each step in Query (Power BI Desktop)

An aternative workaround would be to create your query and next, in the Advanced Editor, add some row count steps after steps you want to verify.

My guess is that there would be a limited number of steps that could possibly influence the number of rows in your table, so you don't need to do it after each query step.

You can (de)comment the row count steps as required.

 

    C0010 = Table.RowCount(PreviousStep),

You may also want to check again when you refresh your data: maybe rows won't be added with the current snapshot, but they might be with future data.

 

Specializing in Power Query Formula Language (M)
6 REPLIES 6
Back2Basics Member
Member

Re: Row count at each step in Query (Power BI Desktop)

I think you can load more or at the end of each query oad the data into the model and see the output in there.

 

It displays as 999+ because it is only showing a small number of the values so that you can see how the change in the query alters the data. I think the only way round this is loading all of the data in after each step and making a note of the rows manually.

dariuszszyc Frequent Visitor
Frequent Visitor

Re: Row count at each step in Query (Power BI Desktop)

Do you think this is an optimal solution in any way ?

 

Also, what do you mean by saying

 


@Back2Basics wrote:

I think the only way round this is loading all of the data in after each step and making a note of the rows manually.


 

??

 

If my query has 50 steps and I want to make sure that after step 15 (merging) I still have the same number of rows (let's imagine this is expected) - I can't imagine removing all steps back to step 15 to load it fully into the model ?

 

If I'd want to confirm the same thing in other steps (let's say 20, 30, 40) - that would be a terrible way to ensure how many rows I have.

 

Perhaps there is some space in Query for additional function:

I can imagine that it shows 999+ when there are more than 999 rows, but when I click "999+" it would calculate exact number.

 

Regards,

Darek

v-jiascu-msft Super Contributor
Super Contributor

Re: Row count at each step in Query (Power BI Desktop)

Hi @dariuszszyc,

 

I think this is good for performance. If all the tables of a very large dataset were imported to the Query Editor, that would be very slow. There are two workarounds here we can get the total rows of a table. Please have a try.

  1. Use function “Count Rows” (Red rectangle in the picture). There is a disadvantage that we have to delete this step later. (We could insert a step and then delete it to satisfy your latest post.)
  2. Create a new table to show the total rows of special table. Every time we click this table, it will show us the total rows of the latest status of the special table. (Blue rectangle in the picture)

New Source -> Other -> Blank Query -> Input “= Table.RowCount(#"Customer")”

Row count at each step in Query (Power BI Desktop) .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarcelBeug Super Contributor
Super Contributor

Re: Row count at each step in Query (Power BI Desktop)

An aternative workaround would be to create your query and next, in the Advanced Editor, add some row count steps after steps you want to verify.

My guess is that there would be a limited number of steps that could possibly influence the number of rows in your table, so you don't need to do it after each query step.

You can (de)comment the row count steps as required.

 

    C0010 = Table.RowCount(PreviousStep),

You may also want to check again when you refresh your data: maybe rows won't be added with the current snapshot, but they might be with future data.

 

Specializing in Power Query Formula Language (M)
dariuszszyc Frequent Visitor
Frequent Visitor

Re: Row count at each step in Query (Power BI Desktop)

That works for me, thanks @MarcelBeug

mtomlinson Regular Visitor
Regular Visitor

Re: Row count at each step in Query (Power BI Desktop)

Just to add to this, you can turn on "Column profile" under "View" in the Query Editor. This gives a count of values in any particular column. (As long as you have "Column profiling based on entire dataset" turned on)

 

Bit quicker than adding a new custom column.

 

Capture.PNG

 

Capture2.PNG

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 21 members 731 guests
Please welcome our newest community members: