Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (17.6k points)

I have a query that returns a dynamic number of columns. I need to dynamically add the same amount of custom columns. I have successfully gotten this far. I'm stuck creating the formulas for the custom columns. This is what I have so far. (This is not the actual query, this is simplified)

What sample query looks like

Here is the Code:

Test = List.Accumulate(MyList, Source, 

       (state, current) => Table.AddColumn(

           state, "A Temp" & Number.ToText(current), each [A1])


For now, I just added [A1] as a place holder for the formula. I need the formula to accumulate as follows:

A Temp1 = [A1] / [TOTAL]

A Temp2 = [A2] / [TOTAL]

A Temp3 = [A3] / [TOTAL] 

The above is not the actual code. Just what I need the formulas to do for each custom column.

Is this possible? I have tried everything I could think of. I'm using power query in excel BTW. 

1 Answer

0 votes
by (47.2k points)

Test = List.Accumulate(

           List.Select(Table.ColumnNames(Source), each _ <> "TOTAL"),


           (state, current) => Table.AddColumn(state,

                                   "Temp " & current,

                                   each Record.Field(_, current) / [TOTAL]))

  •  It gives column names like Temp A1 instead of A Temp1

Browse Categories