CODEWITHSUNDEEP
powerbipowerquerym
Jim
Jim

Reputation: 33

Stuck adding formula to dynamically added columns

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]

Above is not 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.

Upvotes: 3

Views: 222

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

This isn't exactly what you asked for, but I think it will help.

Test = List.Accumulate(
           List.Select(Table.ColumnNames(Source), each _ <> "TOTAL"),
           Source,
           (state, current) => Table.AddColumn(state,
                                   "Temp " & current,
                                   each Record.Field(_, current) / [TOTAL]))

It's not exactly what you asked for as it gives column names like Temp A1 instead of A Temp1.

Upvotes: 1

Related Questions