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-25T13:23:58.873

2

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

edited Apr 25 '19 at 13:23

answered Apr 25 '19 at 11:31

Olly

7,749
1
19
38

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 Apr 28 '19 at 21:41
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!! – Daniel Apr 29 '19 at 00:16
Edit your question to show some sample data, and an expected output. – Olly Apr 29 '19 at 07:09
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 – Daniel May 04 '19 at 00:39

score 2 · Answer 2 · edited Jan 03 '23 at 14:04

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!

Power Query: pass column name as function parameter

2 Answers2

Linked