Looping through reusable steps

[ferryv]

I have a question relating looping with reusable steps.

The scenario is as follows: in order to perform multi hierarchical addition, I need to perform a few steps:

- aggregate totals of child elements 

- find the correct value of the item 

- adding that to the existing value 

- ensure it's a numeric value 

- doing some cleanup and adding controls to ensure only items not processed properly are calculated when running the query again for the next level (otherwise it gets really slow).

There are about 8 steps that are repeated with the only difference being the first step, which needs to reference the previous completed step (ie an incremental process).

While it could be done via eg list.aggregate, I am not able to change the step in the loop.

For example:

- the first time, the step is for example (code is more complex though):

step1 = Table.AddColumn(someStep,....)

- the second time, the step is for example:

step2 = Table.AddColumn(step1,....)

Loop through it again it should be:

step3 = Table.AddColumn(step2,....)

And so on.

I want to use a distinct list to ascertain how many times the loop needs to run. 

Is there a way to use eg a string to specify a step name? For example #"step" & text.From([i]) and #"step" & text.From([i]-1) (where i is a variable defined in the loop). Currently I get token equal espected or circular reference errors when trying different options

- The above throws a Token equal error when trying to use #"step" & text.From([i])  as the step name in 

#"step" & Text.From([i]-1)  = Table.AddColumn(#"step" & Text.From([i])... 

and I get a cyclic reference when the following in the loop

NameOfLastStepBeforeLoop = ...
Loop = List.Generate(..., each let 
AnotherStep  = Table.AddColumn(NameOfLastStepBeforeLoop,... 
...
NameOfLastStepBeforeLoop= FinalStep
in NameOfLastStepBeforeLoop)

[ferryv]

Thanks for the idea. Will mark this as a solution.

Did it this way for a specific use case, where I have this info linked to another data source external to the application that provides the effort data and enable to propagate the data into a compound table, linking data from the 2 sources, eg linking the effort from this source to KPI info, etc from another source.

Will also look at DAX as it is good to have options.

Would be interested in knowing if it would be possible to dynamically reference/create steps for looping in case othe use cases arise.

[ferryv]

Essentially, I am creating rollup information.

Source - columns with "Expected ..." show what I am planning on achieving.: 

The code to populate the table with the missing data is:

let
Source = Excel.Workbook(File.Contents(#"spreadsheetPath"), null, true),
#"Work Items_Sheet" = Source{[Item="Work Items",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Work Items_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Type", type text}, {"Effort", Int64.Type}, {"Parent", Int64.Type}, {"ID", Int64.Type}, {"Expected Effort", Int64.Type}}),

HasChildren = Table.AddColumn(#"Changed Type","HasChildren", each if Table.Contains(#"Changed Type",[Parent=[ID]]) = true then "Yes" else "No"),

ChildData = Table.AddColumn(HasChildren,"ChildData", each null),
FilteredChildData = Table.AddColumn(ChildData,"FilteredChildData", each null),
NewEffort = Table.AddColumn(FilteredChildData,"New Effort", each [Effort]),
ChildEffort = Table.AddColumn(NewEffort,"Child.Effort", each if [HasChildren]="No" then {[Effort]} else null),
NewStartDate = Table.AddColumn(ChildEffort,"New StartDate", each [Start Date]),
ChildStartDate = Table.AddColumn(NewStartDate,"Child.StartDate", each if [HasChildren]="No" then {[Start Date]} else null),
NewEndDate = Table.AddColumn(ChildStartDate,"New EndDate", each [End Date]),
ChildEndDate = Table.AddColumn( NewEndDate,"Child.EndDate", each if [HasChildren]="No" then {[End Date]} else null),

Task = AppendNewEstimate(ChildEndDate),
Story = AppendNewEstimate(Task),
Feature = AppendNewEstimate(Story),

Cleanup = Table.RemoveColumns(Feature,{"ChildData","FilteredChildData","Child.Effort","Child.StartDate","Child.EndDate"})

in
Cleanup

And function  AppendNewEstimate is:

(stepName as table)=>
let
AddChildEstimateColumn = Table.ReplaceValue(stepName,each null,each if [Child.Effort] is null then Table.Group( stepName,{"Parent"}, {{"Retrieved.Child.Effort",each List.Sum([New Effort])},{"Retrieved.Child.StartDate",each List.Min([New StartDate])},{"Retrieved.Child.EndDate",each List.Max([New EndDate])}}) else null,Replacer.ReplaceValue,{"ChildData"}),
FilterChildData = Table.ReplaceValue(AddChildEstimateColumn,each null, each if [Child.Effort] is null then let id=[ID] in Table.SelectRows([ChildData], each [Parent]=id) else null,Replacer.ReplaceValue,{"FilteredChildData"}),

ExpandEffort = Table.ReplaceValue(FilterChildData, each null, each if [Child.Effort] is null then Table.Column([FilteredChildData],"Retrieved.Child.Effort") else [Child.Effort], Replacer.ReplaceValue,{"Child.Effort"}),
ConvertEffort = Table.TransformColumns(ExpandEffort,{"Child.Effort", each Text.Combine(List.Transform(_, Text.From)) , type number}), 
ReplaceEffort = Table.ReplaceValue(ConvertEffort,each null,each [Child.Effort],Replacer.ReplaceValue,{"New Effort"}),
UpdateNewEffortType = Table.TransformColumnTypes(ReplaceEffort,{{"New Effort", type number}}),

ExpandStartDate = Table.ReplaceValue(UpdateNewEffortType, each null, each if [Child.StartDate] is null then Table.Column([FilteredChildData],"Retrieved.Child.StartDate") else [Child.StartDate], Replacer.ReplaceValue,{"Child.StartDate"}),
ConvertStartDate = Table.TransformColumns(ExpandStartDate,{"Child.StartDate", each Text.Combine(List.Transform(_, Text.From)) , type date}), 
ReplaceStartDate = Table.ReplaceValue(ConvertStartDate,each null,each [Child.StartDate],Replacer.ReplaceValue,{"New StartDate"}),
UpdateNewStartDateType = Table.TransformColumnTypes(ReplaceStartDate,{{"New StartDate", type date}}),

ExpandEndDate = Table.ReplaceValue(UpdateNewStartDateType, each null, each if [Child.EndDate] is null then Table.Column([FilteredChildData],"Retrieved.Child.EndDate") else [Child.EndDate], Replacer.ReplaceValue,{"Child.EndDate"}),
ConvertEndDate = Table.TransformColumns(ExpandEndDate,{"Child.EndDate", each Text.Combine(List.Transform(_, Text.From)) , type date}), 
ReplaceEndDate = Table.ReplaceValue(ConvertEndDate,each null,each [Child.EndDate],Replacer.ReplaceValue,{"New EndDate"}),
UpdateNewEndDateType = Table.TransformColumnTypes(ReplaceEndDate,{{"New EndDate", type date}}),

ClearChildData = Table.ReplaceValue(UpdateNewEndDateType,each [ChildData],each null,Replacer.ReplaceValue,{"ChildData"}),
ClearFilteredChildData = Table.ReplaceValue(ClearChildData,each [FilteredChildData],each null,Replacer.ReplaceValue,{"FilteredChildData"}),
ClearChildEffort = Table.ReplaceValue(ClearFilteredChildData,each [Child.Effort],each if [New Effort] is null then null else {[New Effort]},Replacer.ReplaceValue,{"Child.Effort"}),
ClearChildStartDate = Table.ReplaceValue(ClearChildEffort,each [Child.StartDate],each if [New StartDate] is null then null else {[New StartDate]},Replacer.ReplaceValue,{"Child.StartDate"}),
ClearChildEndDate = Table.ReplaceValue(ClearChildStartDate,each [Child.EndDate],each if [New EndDate] is null then null else {[New EndDate]},Replacer.ReplaceValue,{"Child.EndDate"})
in ClearChildEndDate

 Now. It all works. However, the challenge is that in order for it to work, I need to call the function multiple times, once for each level:

Task = AppendNewEstimate(ChildEndDate),
Story = AppendNewEstimate(Task),
Feature = AppendNewEstimate(Story),

If it can be rewritten as a loop, calling different steps, there is no dependency on calling the function a specific number of times and makes it more generic, but eg #"step" & Text.From([i]-1) is not allowed before the equal sign.

Hope this helps.

Note that the top part works OK, so if it is not possible to create a dynamic step or recurring loop that takes the previous result as the first step of the next result, it is not a major issue, but means potentially additional overhead.

[AlexisOlson · Accepted solution]

Personally, I'd do this in DAX rather than Power Query. There are some nice PATH functions that make parent-child hierarchies fairly easy to work with. See here: https://www.daxpatterns.com/parent-child-hierarchies/

Solving this in Power Query is also possible but I need to think about it a bit more.

[AlexisOlson]

For your end result, do you really want a bunch of new columns or are they helper columns to compute the next step and you intend to discard or ignore them once you're done?

[ferryv]

The Table.AddColumn was also more to illustrate what I am attempting to achieve, rather than the actual function, where I replace values in existing columns.

The main issue I'm attempting to resolve is using the last step in the loop as the start of the next iteration to avoid copying/pasting steps or using a separate function, which might cause a external source error once published.

[AlexisOlson]

You might be able to do dynamic step naming using Expression.Evaluate somehow, but it's unlikely that will be a good solution. It's really hard to tell without knowing what you're actually trying to do though.

List.Generate is pretty powerful itself without that sort of thing. If you can create a specific example and desired output, I'll give take a look.

[ferryv]

They are helper columns that I remove at the end and used them to test that the data was running through the process ok.