Forum Discussion
Power Query - get column name
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.
5 Replies
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 FinalTextIf the name of the function is fn_Len, then you can invoke it as a new custom column like this:
fn_Len(_, "Job Name")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 FinalTextI 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"
