Forum Discussion
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
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.
9 Replies
For future readers, just to crank this up a notch, let's say you have a 100-step query with 40 merges (I know, right!), and you just want to see your row counts change over the entire thing without too much effort. Well then, give this a whizz:
-1- Duplicate your query
-2- Open Advanced Editor on your copy and change the 'let' to '[', and your 'in lastStep' to ']'. So:
let Source = mySource, someStuff = doTransformMagic in someStuff ...goes to: [ Source = mySource, someStuff = doTransformMagic ]This will convert your query into a record, with each step being its own row, something like this:
-3- Convert your record into a table and add a custom column that is just = Table.RowCount([Value])
Et Voila:
Then, to update later when new steps are added, you can just copy your current query between let and in, and paste it into your copy between [ and ].
Pete
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.
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
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.
- 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.)
- 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")”
Best Regards!
Dale
