Forum Discussion
How many Applied Steps do you use for a typical query?
I've just worked my way through a somewhat more advanced task and it took me 12 steps to complete. I'm wondering if this is excessive and if I should aim to use less?
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.
8 Replies
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.
