Power Query: pass column name as function parameter

Question

I'm struggling to pass column names to a power query function - if I pass them as [columnname] within the function call, it tries to implement them outside the function; if I pass using [parameter] within the function itself, it looks for a column "parameter" and not the column name that's passed in the call.

Elsewhere, StackExchange recommends using Table.Column(table,columnname) to get around this, but the I get the whole column in one go, rather than being able to compare corresponding entries within the column.

Can anyone help? Here's the code

(table,Mt0,Mt1) => let 
    #"Add Yt.1" = Table.AddColumn(table, "placeholder", 
        each if Table.Column(table,Mt0) <= 0 or - Table.Column(table,Mt1) < Table.Column(table,Mt0)
            then Table.FromRecords({[Mt0 = Table.Column(table,Mt0), Mt1 = Table.Column(table,Mt1)]})
        else Table.FromRecords({[Mt0 = 0,Mt1 = Table.Column(table,Mt1) + Table.Column(table,Mt0)]}))/*,
    #"Expand Yt.1" = Table.ExpandTableColumn(Table.RemoveColumns(#"Add Yt.1",{"Mt0", "Mt1"}), "placeholder", {"Mt0","Mt1"})*/
in #"Add Yt.1"

This is stored as Query1 and I'm calling using Query1(tablename,Y1M,Y2M)

This version generates an error: We cannot apply operator "<=" to types List and Number (so while it may be referencing column Y1M as intended, it's comparing the whole column (as a list) to '0' in the first inequality, rather than one entry at a time.

Help!!

Olly · Accepted Answer · 2019-04-25 13:23:58Z

2

Try using Record.Field(_, Mt0) instead of Table.Column

edited Apr 25, 2019 at 13:23

answered Apr 25, 2019 at 11:31

Olly

7,9021 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Over a year ago

Thanks Olly - that's a huge help. The error message is gone, but now there is a next problem! Table.FromRecords is passing Mt0 and Mt1 as column names instead of calling the function parameters named Mt0 and Mt1 - any advice?

Daniel Over a year ago

Okay happy to take any pointers on the above, though I'm currently using a workaround to simply rename them from Mt0/Mt1 to the parameter names in the expansion step What's undoing me now is that Table.FromRecords doesn't seem to like when I create an entry: Mt2 = Record.Field(, Mt2) + Record.Field(, Mt1) It can handle Mt2 = [Y2M] + [Y1M] (which is what the entry should resolve to) but when using Record.Field I get an error "We cannot convert the value 0 to type Text". [Y1M] and [Y2M] are both valid integers. Help!!

Olly Over a year ago

Edit your question to show some sample data, and an expected output.

Daniel Over a year ago

Kudos my friend - thanks for all your help. How do I acknowledge you formally? The other issue I experienced was a naming conflict, so changed the names of the columns in the created table to something more anodyne and the conflict resolved. Thanks again for all your help. D

mark fitzpatrick · Accepted Answer · 2023-01-03 14:04:11Z

2

This was very helpful. This was making me a little nuts. Code that doesn't work followed by one that does.

This does not work

(tablename as table, columnname as text)=>

let
// results in new column with all values null 
Source = tablename,
GetColumn = Table.AddColumn(Source, "Text", each ```Text.Middle(columnname, 32, 7 ) , type text)
in
GetColumn

This does work, using (Record.Field(_, columnname))

(tablename as table, columnname as text)=>
let
// results in new column with characters properly ```extracted from the column specified with ```columnname when invoking the function
Source = tablename,
GetColumn = Table.AddColumn(Source, "Text", 
each Text.Middle(Record.Field(_, columnname), 32, 7), type text)
in
GetColumn

Thank you!

edited Jan 3, 2023 at 14:04

mark fitzpatrick

3,3923 gold badges14 silver badges24 bronze badges

answered Dec 8, 2020 at 17:45

Steve Ross

211 bronze badge

Collectives™ on Stack Overflow

Power Query: pass column name as function parameter

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related