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.
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.
[email protected] Q1 Correct [email protected] Q2 Incorrect [email protected] Q1 Incorrect [email protected] Q2 Incorrect [email protected] Q1 Correct [email protected] Q2 Correct [email protected] Q1 Incorrect [email protected] Q2 Correct [email protected] Q2 Incorrect [email protected] Q1 Correct [email protected] Q2 Incorrect [email protected] Q1 Correct Incorrect [email protected] Q2 Incorrect Correct [email protected] Q1 Incorrect [email protected] Q2 Incorrect Incorrect [email protected] Q1 Correct [email protected] Q2 Correct [email protected] Q1 Correct [email protected] Q2 Incorrect I've got two tables, Table1 and Table2 which each have a set of email addressess, questions, and how people responded which is shown in the final table. Column 3 of the last table is column 3 of Table1, and column 4 of the last table is column 3 of Table2. This is what I've got thus far:
let Table1 = Table.AddColumn(Sheet1, "Response_Unique", each "S1"&[Response]), Table2 = Table.AddColumn(Sheet2, "Response_Unique", each "S2"&[Response]), Source = Table.Combine({Table1, Table2}), #"Added Custom" = Table.AddColumn(Source, "Merge_ID", each [Email]&"-"&[Question]), #"Grouped Rows" = Table.Group(#"Added Custom", {"Merge_ID"}, {{"Merged Responses", each Text.Combine([Response_Unique], ""), type text}}), Joined = Table.Join(#"Added Custom", "Merge_ID", #"Grouped Rows", "Merge_ID"), #"Split Column by Delimiter" = Table.SplitColumn(Joined,"Merged Responses",Splitter.SplitTextByDelimiter("S2", QuoteStyle.Csv),{"Merged Responses.1", "Merged Responses.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged Responses.1", type text}, {"Merged Responses.2", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","S1","",Replacer.ReplaceText,{"Merged Responses.1"}), #"Removed Duplicates" = Table.Distinct(#"Replaced Value", {"Merge_ID"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Duplicates",{{"Merged Responses.1", "Sheet 1 Response"}, {"Merged Responses.2", "Sheet 2 Response"}}), #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns1","",null,Replacer.ReplaceValue,{"Sheet 1 Response"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Response", "Response_Unique", "Merge_ID"}) in #"Removed Columns"This is my first adventure into the Advanced Editor and making something a bit more complicated than just a new column. I'm guessing then that this is just the way things go with Power BI when you're dealing with data sources that aren't so organized.
travbum As mentioned. The number of steps shouldn't be the initial concern. Performance should be. If you need to clean the data, then you need the steps. If the performance is horrible and you are asking about better ways to do something, I'm sure someone can help. But quantity of steps alone shouldn't worry you other than it would take more time to come back and figure out.
travbum I don't see anything obvious that I would do differently to make that more efficient, though some here might spot something I missed. I might change the order slightly so that similar steps go together when possible (e.g. do all the Replace Value stuff in a row before moving on) but that's a purely cosmetic thing. How long does it take to refresh this?
Messy input definitely does make for long queries. If you have some Advil on hand I'll tell you about that 20-something step query of mine.
This question is akin to asking how many licks does it take to get to the center of a Tootsie Pop. Some steps are tiny and others are the large bite at the end. Each data set is different and needs to be treated as such. My UPS file tops out at 22 steps but each one is necessary and without it, I would be dealing with crap data.
If it works, it works.
