How to avoid hardcoding names when using List.Generate with multiple dependent columns

[Nilselmano]

Hi,
I will try to explain my issue as best as i can.

I want to perform a calculation that is basically a running total but subtracting an amount in each iteration that is dependent on the amount in the other value columns present. Basically it means that all value columns in the original table needs to be included in the List.Generate loop in order to get to the right result. 

I want to make this dynamic as the amount of value columns is not constant, so i want to build the logic but without hardcoding names along the way.

I managed to get the intended result with hardcoded names and the query is:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxUNJRMjQAk0YQtlKsTrSSMYgNw1AxioSMKDLMiFrOoL8QZR6nSoiZEKE1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [no1 = _t, no2 = _t, no3 = _t, Factor = _t]),
  Tbl_Start = Table.TransformColumnTypes(Source, {{"no1", Int64.Type}, {"no2", Int64.Type}, {"no3", Int64.Type}, {"Factor", Int64.Type}}, "da"),
  BuffTbl = Table.Buffer(Tbl_Start),
  IndexCol = Table.AddIndexColumn(Tbl_Start, "Index", 0, 1, Int64.Type),
  RunningTotal = List.Generate (
  () => [ RT_no1 = Tbl_Start{0}[no1], 
          RT_no2 = Tbl_Start{0}[no2], 
          RT_no3 = Tbl_Start{0}[no3], 
          factor = Tbl_Start{0}[Factor], 
          factor_no1 = (RT_no1/(RT_no2+RT_no3+RT_no1))*factor, 
          factor_no2 = (RT_no2/(RT_no2+RT_no3+RT_no1))*factor, 
          factor_no3 = (RT_no3/(RT_no2+RT_no3+RT_no1))*factor, 
          Index = 0],                                 // Initial values

   each [Index] < List.Count( Tbl_Start[Factor] ),    // Do while

   each [                                             
    RT_no1 = List.Sum( { [RT_no1], Tbl_Start{[Index]+1}[no1] - [factor_no1] } ),
    RT_no2 = List.Sum( { [RT_no2], Tbl_Start{[Index]+1}[no2] - [factor_no2] } ),
    RT_no3 = List.Sum( { [RT_no3], Tbl_Start{[Index]+1}[no3] - [factor_no3] } ), 

    factor_no1 = (RT_no1/(RT_no2+RT_no3+RT_no1))*Tbl_Start{Index}[Factor],
    factor_no2 = (RT_no2/(RT_no2+RT_no3+RT_no1))*Tbl_Start{Index}[Factor],
    factor_no3 = (RT_no3/(RT_no2+RT_no3+RT_no1))*Tbl_Start{Index}[Factor],

    Index = [Index] + 1],                             // Calculations

   each [[RT_no1], [RT_no2], [RT_no3], [factor_no1], [factor_no2], [factor_no3]] ),
  // Records to return
  Output = Table.FromColumns(
     Table.ToColumns( Tbl_Start )   & { RunningTotal } ,    // Combine Tables
     Table.ColumnNames( Tbl_Start ) & { "RT" } ),
  Expanded = Table.ExpandRecordColumn(Output, "RT", Record.FieldNames(Output[RT]{0}), Record.FieldNames(Output[RT]{0}))
in
  Expanded

Expected result:

no1	no2	no3	Factor	RT_no1	RT_no2	RT_no3	factor_no1	factor_no2	factor_no3
140	100	120	10	140,00	100,00	120,00	3,89	2,78	3,33
30	0	0	10	166,11	97,22	116,67	4,37	2,56	3,07
0	0	0	10	161,74	94,66	113,60	4,37	2,56	3,07
0	0	0	10	157,37	92,11	110,53	4,37	2,56	3,07
0	0	0	10	153,00	89,55	107,46	4,37	2,56	3,07
0	20	0	10	148,63	106,99	104,39	4,13	2,97	2,90
0	0	0	10	144,50	104,02	101,49	4,13	2,97	2,90
0	0	0	10	140,37	101,04	98,59	4,13	2,97	2,90
0	0	20	10	136,24	98,07	115,69	3,89	2,80	3,31
0	0	0	10	132,35	95,27	112,38	3,89	2,80	3,31

Any hints to how I can achieve will be greatly appreciated.

[lbendlin]

Whenever you have volatile number of columns you need to unpivot. Here is an example of the RT computation

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxUNJRMjQAk0YQtlKsTrSSMYgNw1AxioSMKDLMiFrOoL8QZR6nSoiZEKE1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [no1 = _t, no2 = _t, no3 = _t, Factor = _t]),
  IndexCol = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexCol, {"Factor", "Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Factor", type number}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "RT", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Index]<=k[Index] and [Attribute]=k[Attribute])[Value]), type number)
in
    #"Added Custom"

I'll leave the factor computation up to you.

Frankly neither is necessary to do in Power Query - you can do these much easier in DAX.