Power Query - get column name

[Anonymous]

Hello,

I have different files containing various attributes (one file will have Job Name, another one will have Location, another one will have Grade, etc.).

Sample Data:

Job Name

Controller

Director

Analyst

VP Of Finance

Director

I want to have a function that will add a column in which each cell will have combined: the Column Name (Job Name in case of the above sample) AND length of the Column Name (Length of each Job Name in case of the above sample).

Job Name Desired Output

Controller	Job Name 10
Director	Job Name 8
Analyst	Job Name 7
VP Of Finance	Job Name 13
Director	Job Name 8

What I have is:

(MyColumn) as text =>

let

Source = Text.Length(MyColumn),
NewSource = Number.ToText(Source),
FinalText = Text.Combine({MyColumn,NewSource}," ")

in FinalText

I need to find a way to reference the column name to be added to the FinalText, because for now the output is following:

Job Name Current Output (incorrect!)

Engineer	Engineer 1
Director	Director 2
Analyst	Analyst 3
Engineer	Engineer 1

Btw I tried using Record.Field(_, MyColumn) but it didn't work with below error:

An error occurred in the ‘’ query. Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

Thank you in advance for help.

[Anonymous]

@AlexisOlson Thank you for your help.

It all looks good, but one thing I would like to change. Is there a way to avoid handwriting "Job Name"? 

In my example (below), I am already passing the column name as a Parameter. Can we somehow use this variable to be used automatically in the output (FinalText)?

As an example, If my parameter (MyColumn) is Job Name I want the first argument of Text.Combine to be referenced as JobName, but in the below function it is taking the cell (row) value, instead of the MyColumn.

(MyColumn) as text =>

let

Source = Text.Length(MyColumn),
NewSource = Number.ToText(Source),
FinalText = Text.Combine({MyColumn,NewSource}," ")

in FinalText

[AlexisOlson]

I don't quite follow. You need to specify "Job Name" once so the function knows what column you want to read text lengths from. There's no other place in what I suggested that has "Job Name" hard coded and the output is what you specified.

Maybe if I rewrite it a bit more like your code it will be slightly clearer?

(R as record, MyColumn as text) as text =>               //R = row 1, MyColumn = "Job Name"
let
    Text = Record.Field(R, MyColumn),                    //Returns R[Job Name] = "Controller"
    Source = Text.Length(Text),                          //Returns length("Controller") = 10
    NewSource = Number.ToText(Source),                   //Returns "10"
    FinalText = Text.Combine({MyColumn, NewSource}, " ") // "Job Name" & " " & "10"
in
    FinalText                                            // "Job Name 10"

[Anonymous]

Sorry for the late reply.

In this function, I have two parameters: R & MyColumn. MyColumn works as a drop-down so I can pick a column from the drop-down list, but R is a parameter that I have to hand-write. See below:

[AlexisOlson]

Unfortunately, I can't think of any way to make this work using the Invoke Custom Function GUI and zero manual typing but typing fn_Len(_, "Job Name") in the Custom Column GUI isn't that bad (use Query1 instead of fn_Len if that's what your function is named).

[AlexisOlson]

This doesn't work well with just a single input parameter. Try writing where you pass in the record too.

(R as record, MyColumn as text) as text =>
let
    Text = Record.Field(R, MyColumn),
    TextLen = Number.ToText(Text.Length(Text)),
    FinalText = MyColumn & " " & TextLen
in
    FinalText

If the name of the function is fn_Len, then you can invoke it as a new custom column like this:

fn_Len(_, "Job Name")