locked
adding multiple calculating custom columns in one Applied Step RRS feed

  • Question

  • We have a set of data that has been linked to multiple data sources and grouped to a common level with values we need to run maths against.  The data has a single totaling field and we wish to multiply 4 fields to it and derive 4 fields with the results of each calculation at the end of the Grouped Rows data

    = Table.AddColumn(#"Grouped Rows", "UNITS_LMITRACTGOAL", each [TTLCT_TTL]*[LMITRACT_GOAL])

    = Table.AddColumn(#"Added Custom", "UNITS_LMIBORGOAL", each [TTLCT_TTL]*[LMIBOR_GOAL])

    = Table.AddColumn(#"Added Custom1", "UNITS_MMCTGOAL", each [TTLCT_TTL]*[MINORTRACT_GOAL])

    = Table.AddColumn(#"Added Custom2", "UNITS_MINORBORGOAL", each [TTLCT_TTL]*[MINORBOR_GOAL])We were able to create the Grouped Rows table by nesting multiple groupings into one set of code to create the table, as below. It was created by selecting the grouping button and selecting one group column and creating multiple summing columns and average columns.  we would like to multiple 2 of the resulting columns 4 times.

    = Table.Group(#"Renamed Columns1", {"AA"}, {{"LMICT_TTL", each List.Sum([LMICT_CT]), type number},{"LMIAMT_TTL", each List.Sum([LMICT_AMT]), type number},{"LMIBOR_TTL", each List.Sum([LMIBOR_CT]), type number},{"LMIBORAMT_TTL", each List.Sum([LMIBOR_AMT]), type number},{"MMCT_TTL", each List.Sum([MMCT_CT]), type number},{"MMCTAMT_TTL", each List.Sum([MMCT_AMT]), type number},{"MINOR_TTL", each List.Sum([MINOR_CT]), type number},{"MINORAMT_TTL", each List.Sum([MINOR_AMT]), type number},{"TTLCT_TTL", each List.Sum([TTL_CT]), type number},{"LMITRACT_GOAL", each List.Average([HighRisk_List.LMI_Tract_Goal]), type number},{"LMIBOR_GOAL", each List.Average([HighRisk_List.LMIBor_Goal]), type number},{"MINORTRACT_GOAL", each List.Average([HighRisk_List.Minor_Tract_Goal]), type number},{"MINORBOR_GOAL", each List.Average([HighRisk_List.Minor_Bor_Goal]), type number}})

    Is there a way to do this similarly for multiple calculated columns?  In a fashion like below:

    = Table.AddColumns(#"Grouped Rows", {{"UNITS_LMITRACTGOAL", each [TTLCT_TTL]*[LMITRACT_GOAL]},{"UNITS_LMIBORGOAL", each [TTLCT_TTL]*[LMIBOR_GOAL]},{"UNITS_MMCTGOAL", each [TTLCT_TTL]*[MINORTRACT_GOAL]},{"UNITS_MINORBORGOAL", each [TTLCT_TTL]*[MINORBOR_GOAL]}})

    thanks

    Thursday, October 22, 2020 4:58 PM

Answers

  • Mi Marty

    There isn't a Table.AddColums funtion and Table.AddColumn doesn't take any List argument to do what you want. Alternatively you can do it as follow (the below code will add your first columns):

    = List.Accumulate(
        {
            // New column name      FieldName1      FieldName2
            {"UNITS_LMITRACTGOAL",  "TTLCT_TTL",    "LMITRACT_GOAL"},
            {"UNITS_LMIBORGOAL",    "TTLCT_TTL",    "LMIBOR_GOAL"},
            {"UNITS_MMCTGOAL",      "TTLCT_TTL",    "MINORTRACT_GOAL"},
            {"UNITS_MINORBORGOAL",  "TTLCT_TTL",    "MINORBOR_GOAL"}
            // Add the field names (as above) for your other columns below
        }, #"Grouped Rows",
        (State,Current)=> Table.AddColumn(State,Current{0}, each Record.Field(_,Current{1})*Record.Field(_,Current{2}), type number)
    )

    Thursday, October 22, 2020 8:43 PM
  • Maybe this function I wrote sometime ago could work for what you're trying to do.
    Usage:
    FunctionName(
        #"Grouped Rows",
        { "LMITRACT_GOAL", "LMIBOR_GOAL", "MINORTRACT_GOAL", "MINORBOR_GOAL" },
        (t) => "UNITS_" & Text.Remove(t, {"_"}),
        each [_][Value] * [#"TTLCT_TTL"],
        Number.Type
    )


    Friday, October 23, 2020 6:48 AM

All replies

  • Mi Marty

    There isn't a Table.AddColums funtion and Table.AddColumn doesn't take any List argument to do what you want. Alternatively you can do it as follow (the below code will add your first columns):

    = List.Accumulate(
        {
            // New column name      FieldName1      FieldName2
            {"UNITS_LMITRACTGOAL",  "TTLCT_TTL",    "LMITRACT_GOAL"},
            {"UNITS_LMIBORGOAL",    "TTLCT_TTL",    "LMIBOR_GOAL"},
            {"UNITS_MMCTGOAL",      "TTLCT_TTL",    "MINORTRACT_GOAL"},
            {"UNITS_MINORBORGOAL",  "TTLCT_TTL",    "MINORBOR_GOAL"}
            // Add the field names (as above) for your other columns below
        }, #"Grouped Rows",
        (State,Current)=> Table.AddColumn(State,Current{0}, each Record.Field(_,Current{1})*Record.Field(_,Current{2}), type number)
    )

    Thursday, October 22, 2020 8:43 PM
  • Maybe this function I wrote sometime ago could work for what you're trying to do.
    Usage:
    FunctionName(
        #"Grouped Rows",
        { "LMITRACT_GOAL", "LMIBOR_GOAL", "MINORTRACT_GOAL", "MINORBOR_GOAL" },
        (t) => "UNITS_" & Text.Remove(t, {"_"}),
        each [_][Value] * [#"TTLCT_TTL"],
        Number.Type
    )


    Friday, October 23, 2020 6:48 AM