Conditional transform MANY dinamic columns based on another columns - feed Record.TransformFields with generated list

Question

I have tried to find solution and tried few thing on my own but I realized that I'm stuck and that I need help.

So first I will describe the table and then the problem.

I need to change columns based on some condition and based on comparison of other columns.

In real case there are many (50-100) columns and they often change names. I have solved how to get list of columns that needs to be transformed so for the purpose of this description I will simplify table and we will assume that columns stay same.

Table1

| Number1 | Number2 | Percent | A | B | C |
|---------|---------|---------|---|---|---|
|         |         |         |   |   |   |
|         |         |         |   |   |   |

I need to change number values in columns A, B, and C if condition is not met, if condition is meet they need to stay as they are.

The condition look like this:

if [Number1] = [Number2] then   A else A * [Percent]
if [Number1] = [Number2] then   B else B * [Percent]
if [Number1] = [Number2] then   C else C * [Percent]

I have used code for specific (in this case 3) number of columns and it worked: (Power Query Transform a Column based on Another Column)

=Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => 
        Record.TransformFields(
            r,
            {
                {"Column A", each  if [Number1] = [Number2] then _ else _ * [Percent]},
                {"Column B", each  if [Number1] = [Number2] then _ else _ * [Percent]},
                {"Column C", each  if [Number1] = [Number2] then _ else _ * [Percent]}
            }
        )
    ),
    Value.Type(Table1)
)

But as I have many columns I would like to feed Record.TransformFields with a generated list.

Is there a way to use List.Transform to generate a list transformOperationsfor Record.TransformFields?

Something like this:

=Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => 
        Record.TransformFields(
            r,
            {
                List.Transform(
                    {"A", "B"},
                    each "{"""& _ & """" & ", each if [Number1] = [Number2] then _ else _ * [Percent])}"
                )           
            }
        )
    ),
    Value.Type(Table1)
)

I also tried with Table.TransformColumns but it didn't work:

= Table.TransformColumns(Table1,List.Transform(
                    {"A", "B"},
                    each "{"""& _ & """" & ", each if [Number1] = [Number2] then _ else _ * [Percent])}"
                )),

I'm guessing that I would need to use List.Accumulate but not figured out yet.

Alexis Olson · Answer 1 · 2020-11-20T14:53:51.923

I think the easiest way to do this is to unpivot all except the first three columns, apply the transformation, and then pivot back.

Try pasting this example into the Advanced Editor as a new query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc5BCsAgDETRu2QtJU1M27uI979G8xNw4TDMU3AtuWXU0ctoSkSGqeyxxA57jxnwbGb0YkaHnfY0z8Nvv8mAv2ZqFHMx4Ij6Rfr+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Number2 = _t, Percent = _t, A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number1", type number}, {"Number2", type number}, {"Percent", type number}, {"A", type number}, {"B", type number}, {"C", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Number1", "Number2", "Percent"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Number1] = [Number2] then [Value] else [Value] * [Percent], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum)
in
    #"Pivoted Column"

Note that I've added a custom column rather than transforming the Value column since it's easier to reference other columns this way than having to do a record transform.

It is possible your way too. I think the syntax would look like this:

= Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => 
        Record.TransformFields(
            r,
            List.Transform(
                {"A", "B", "C"},
                each {_, each if r[Number1] = r[Number2] then _ else _ * r[Percent]}
            )
        )
    ),
    Value.Type(Table1)
)

Notice that I'm transforming each string "X" into a list {"X", function} whereas you were trying to transform it into a string.

Conditional transform MANY dinamic columns based on another columns - feed Record.TransformFields with generated list

1 Answers1