cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

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

ControllerJob Name 10
DirectorJob Name 8
AnalystJob Name 7
VP Of FinanceJob Name 13
DirectorJob 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!)

EngineerEngineer 1
DirectorDirector 2
AnalystAnalyst 3
EngineerEngineer 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 5
Anonymous
Not applicable

@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

 

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
Not applicable

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:

 

MarekRe_0-1663152988192.png

 

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
Super User
Super User

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")

 

AlexisOlson_0-1662655831746.png

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors