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

Question

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.

If you're defining things with reference to multiple environments (e.g. the row record and also the field of a record), then you'll often need a variable like this to distinguish them. What's the reason behind trying to eliminate it? — Alexis Olson
– Alexis Olson, Commented Feb 9, 2021 at 19:58
@AlexisOlson, it can't be replaced with each, because the lower function has no other way of accessing a sibling field? Am I understanding that correctly? I'm just trying to better understand powerquery functions, and seeing if I could make a function I could pass in a list to. — J. M. Becker
– J. M. Becker, Commented Feb 11, 2021 at 5:38
@AlexisOlson, I also see functions that seem to call other functions, yet don't seem to explicitly pass in their parameter... like how you sometimes see FromText used, it takes a paramater but somehow it can be passed in by name without the parameter. — J. M. Becker
– J. M. Becker, Commented Feb 11, 2021 at 5:42

Alexis Olson · Accepted Answer · 2021-02-11 15:10:58Z

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).

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related