powerquery - Determine column on which invoke custom function

Question

I am trying to invoke a function on an added column that will concatenate two columns. The catch is that I can't use the column name shorthand as I use dynamic parameters using strings to determine the column name.

Therefore the result is that I get a column as a List multiplied per row rather than the concatenated value for the specific row as intended

(func as text) =>

let
    Source = Excel.CurrentWorkbook(){[Name="DataTBL"]}[Content],

    \This is the string extraction process for the parameter
    funcTrig = Text.Start(func, 1),
    columnA =  "" & Text.BetweenDelimiters(func,"_","_") & "",
    columnB = "" & Text.AfterDelimiter(func,"_",1) & "",
    
    \converting the string to column data
    Convert2ColA = Table.Column(Source,columnA),
    Convert2ColB = Table.Column(Source,columnB),

    \function to concatanate column A value at a specific row with column B value at the same row.
    concat= StraightForward(Convert2ColA ,Convert2ColB) 
in
    concat

I have outlined with remarks the process and desired results, In the added picture I have pulled out the result of "Convert2ColA" what is the desired result will be 1999 in row one and so on.

question from:https://stackoverflow.com/questions/65921741/determine-column-on-which-invoke-custom-function

Answer 1

Waitting for answers