Search code examples
powerquerym

Recursive expression for multiply multiple columns by same column in table

I have a table - each row has columns for percentages of different categories plus a column for total value, and I want to calculate the value for each category by multiplying the total value by each category percentage.

I have a table, and, for each of the other columns (Market Cap.Large, Market Cap.Medium etc. - all except Symbol) I want to multiply it by the Current Value column. I can do it manually and add a column for each (the #"Added Custom" line below), but the number of columns may increase and the titles may change, so I don't want to have to input a custom column for each.

Is there a recursive expression to create a new column for each of the existing ones, and multiply it by Current Value - I'm guessing it would involve creating a parameter for the column name, but I'm not knowledgeable enough on how to do it.

table

let
    Source = #"Portfolio %",
    #"Merged Queries" = Table.NestedJoin(Source,{"Symbol"},#"Current Output Aggregated",{"Symbol"},"Current Output Aggregated",JoinKind.Inner),
    #"Expanded Current Output Aggregated" = Table.ExpandTableColumn(#"Merged Queries", "Current Output Aggregated", {"Current Value"}, {"Current Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Current Output Aggregated", "Market Cap.Giant Value", each [Market Cap.Giant]*[Current Value])
in
    #"Added Custom"
Solution

  • Untested, written on phone, but I think what you want to do is:

    1. Create a List of columns that you want to multiply by Current Value column (all columns except: Symbol, Current Value).

    2. Loop over this newly created List using List.Accumulate and, in doing so, add a column for each column in the list (by calling Table.AddColumn during each loop iteration).

    So, something like the M code below (copy-paste the below to Advanced Editor if necessary):

    let
    Source = #"Portfolio %",
    mergedQueries = Table.NestedJoin(Source,{"Symbol"},#"Current Output Aggregated",{"Symbol"},"Current Output Aggregated",JoinKind.Inner),
    expandedNested = Table.ExpandTableColumn(mergedQueries, "Current Output Aggregated", {"Current Value"}, {"Current Value"}),
    allHeaders = Table.ColumnNames(expandedNested),
    headersToLoopOver = List.RemoveItems(allHeaders, {"Symbol", "Current Value"})
    loopOverList = List.Accumulate(headersToLoopOver, expandedNested, (tableState, currentColumn) =>
        Table.AddColumn(tableState, currentColumn & " Value", each Record.Field(_, currentColumn) * [Current Value], type number)
    )
in
    loopOverList

    Am not sure if I got theRecord.Field bit right (in the loop) and I don't have access to a machine to test, but it should be easy for you to check if the output is what you expect.