Forum Discussion
How many Applied Steps do you use for a typical query?
Unfortunately that's too vague a question to give a sensible answer to. It's like asking "How much water do I need?" Need for what? To drink today? To survive in the desert for a month? To fill a fish tank?
You need exactly as many steps as it takes to get the result you need in by the most efficient means. In the report I have open there are 14 queries. One of them has two steps, and it's one of the largest and most important tables in the whole model. Another one has 21 steps, plus 6 more steps that take place in a different query that is merged with it on step 15.
A better question might be, "Are my query load times as quick as they could be?" If refreshing your data takes a long time, maybe there's a better way to write your query in fewer steps. But maybe there isn't. Fewer steps also might not be more efficient. Sometimes an extra step or two might make things load faster.
Can you give an example of what this query does and how? Maybe there's a better way.
Thanks for the feedback. As far as refreshing, with 2 Excel Sheets each with 6 rows and 3 columns takes about 1s. The data we're actually working with has about ~60,000 rows from Salesforce though and it honestly seems like that takes up most of the refresh time. Should I run a refresh to see how long Power BI takes to pull all the data from Salesforce and then use that a baseline? If so, if I'm looking at the time after the data has been pulled from Salesforce, after what period of time should I consider my query too slow? 5s? 30s? Something else?
travbum In all honesty, when I hit refresh on the UPS file I have mentioned, I cannot do any other work while it churns. It finishes up in under 3 minutes that way. It is a beast but every one of those steps is necessary. It currently has 360,000 rows and about 8 columns in the largest query. Refresh time is important, more so if you are hitting live tables. I have, in the past, crashed navision here at my company, but, in the grand scheme of things, getting the data cleaned and usable is much more important than time spent in refresh. Now, if your query is crashing the software or the live tables you may want to take a second look . . .