Power Query Function - get column name

MarekMarek 0
Viewed 58

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 Job Location Job Grade
Controller Some values High
Director No Values Low
Analyst Some Other values
VP Of Finance Blank Medium
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).

Desired Output:

Job Name Job Location Job Grade Desired Output
Controller Some values High Job Name 10
Director No Values Low Job Name 8
Analyst Some Other values Job Name 7
VP Of Finance Blank Medium Job Name 13
Director Executive 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.

Edit: I have edited the post, I hope it's more clear now.

m powerquery
2 Answers

Is this what you want?

zzz = Table.AddColumn(#"PriorStepNameHere", "Custom", each Table.ColumnNames(#"PriorStepNameHere"){0} &" "& Text.From(Text.Length(Record.Field(_,Table.ColumnNames(#"PriorStepNameHere"){0}))))
horseyride0

Here is a similar answer.

Table1 looks like this.

Job Name
Controller
Director
Analyst
VP Of Finance
Director 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Output", each Text.Combine({
           Table.ColumnNames(Source){0}," ",
           Text.From(Text.Length(Record.Field(_,Table.ColumnNames(Source){0})))
           }))
in
    #"Added Custom"

Edit 1 from comment:

Based on what you want I recommend that you don't use "Add Column>Invoke Custom Function". Instead in the "Applied Steps" right click and choose "Insert step after" you would then type in the =function_name(prior_step_table,number_of_column) the function is below.

(Table as table, column_n as number) => 
let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Output", each Text.Combine({Table.ColumnNames(Source){column_n}," ",Text.From(Text.Length(Record.Field(_,Table.ColumnNames(Source){column_n})))}))
in
    #"Added Custom"
Shane S0
Related