CODEWITHSUNDEEP
powerquery
J. M. Becker
J. M. Becker

Reputation: 2835

Powerquery, creating parameterized function possible, when needing to access row in example?

Is it possible in PowerQuery, to create a function that begins like this,

(Table as table, transformOperations as list) as table =>

That would allow you to do this sort of operation

Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) =>
            Record.TransformFields(
                r,
                {
                    { "A", each if r[Column B] = "1" then "Z" else _ },
                    { "B", each if r[Column C] = "1" then "Z" else _ }
                }
            )
    )
)

but... would just be able to accept the final list of field transform Operations, and still be able to read other fields in the row? Basically I'm trying to eliminate need for the r variable.

Upvotes: 0

Views: 179

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

It's important for understanding to remember that each ... _ is "syntactic sugar" and just another way of writing (_) => ... _. I.e., the variable name is _.

So we could rewrite your M as

Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) =>
            Record.TransformFields(
                r,
                {
                    { "A", (_) => if r[Column B] = "1" then "Z" else _ },
                    { "B", (_) => if r[Column C] = "1" then "Z" else _ }
                }
            )
    )
)

If you don't have r as a separate variable name, then if you try to access [Column B], then it thinks you are referencing _[Column B], which doesn't exist since _ is a specific field rather than a record with that as one of the fields.

The difficulty here is that the transformations list you have depends on values that come from other fields, so it isn't self-contained.

What you can do is break up the functions like this:

let
    ApplyTransform = (Table as table, rowTransform as function) as table =>
    Table.FromRecords(Table.TransformRows(Table, rowTransform))
in
    ApplyTransform

let
    rowTransform = (Row as record) as record =>
    Record.TransformFields(
        Row,
        {
            { "A", each if Row[Column B] = "1" then "Z" else _ },
            { "B", each if Row[Column C] = "1" then "Z" else _ }
        }
    )
in
    rowTransform

Then you can call them together like ApplyTransform(Table1, rowTransform).

Upvotes: 1

Related Questions