Transform Columns using Dynamic List

[grsmith]

Hi there; I've been banging my head against this for a while:

I am trying to multiply all value columns in my table by -1.  The number of value columns can vary - and I generate a list of column names (NewFieldNames).

I am trying to use the Table.TransformColumns method as below:

= Table.TransformColumns(Source, each {NewFieldNames,  each _* -1 , type number})

I get the error:

  Expression.Error: We cannot convert a value of type Function to type List.
  Details:
  Value=Function
  Type=Type

Any ideas on getting this to work?

[Greg_Deckler]

Maybe:

= Table.TransformColumns(Source, each {NewFieldNames(),  each _* -1 , type number})

Assuming that function returns a list.

[grsmith]

Thanks for the message Greg; unfortunately this doesn't solve the issue - I get the same error as previous.

A bit more experimenting overnight and the below works for the first column (i.e. the first item in the list NewFieldNames):

= Table.TransformColumns(#"Multiplied Column1", {NewFieldNames{0} , each _ * -1, type number})

I understand that the TransformColumns method requires the second parameter to be all functions, so I'm not sure why the above works as it only returns the first string for the first column - i.e. not a function.

I need this to iterate through each list item (i.e. all field names), and apply the transformation (* -1) to each line in the data for that field.  I just don't know how that syntax is written.

[v-yuta-msft · Accepted solution]

Hi grsmith,

Based on your description, you want to multiply all value in your table with -1, right?

You can use R Script below instead:

# 'dataset' holds the input data for this script

len_row <- nrow(dataset)

len_column <- ncol(dataset)

for (i in 1:len_row)

{

    for (j in 1:len_column)

    {

         dataset[i, j] = dataset[i, j] * -1

    }

}

result <- dataset

And please refer to Run R scripts in Power BI Desktop.

Regards,

Jimmy Tao